1 Reply Latest reply: Nov 9, 2018 7:56 AM by Ralph Mundy RSS

    Prior month, final day

    Mitchell Wade

      Help please!

       

      I am trying to return summed data in Qlik Sense for the prior month based on a selected month.

       

      Background

      I have a field called STATUS which has various entries, one of which is NEW BUSINESS and one is INFORCE. Each of these have Net Written Premium figures against them. The NEW BUSINESS entries can be against any day in a month whereas the INFORCE entries are all set to the last day of each month.

       

      Problem

      When the NEW BUSINESS Net Written Premium data is selected I need to return the INFORCE Net Written Premium from the prior month.

       

      STATUSRow Extract DateNet Written Premium
      INFORCE31/01/2018£100
      INFORCE31/01/2018£200
      NEW BUSINESS14/01/2018£50
      NEW BUSINESS20/01/2018£30
      INFORCE31/12/2017£300
      INFORCE31/12/2017£25

       

      Using the data above, if January 2018 was selected, £80 would show as NEW BUSINESS and £325 would show as INFORCE.

       

      Any help will be gratefully received!

       

      Thanks,

       

      Mitch