2 Replies Latest reply: Jan 30, 2015 5:02 AM by Wietze Hoekzema RSS

    Calculating totals over last months

      Hi all,

       

      I have a table with dates and numbers like this:                                                                                                                                                                                                                                                                            

      IndicatorYearMonthFirstDayPeriodNrOfThingsNmbrOfThingsPast2Years
      9920131Jan  1 201310858
      9920132Feb  1 20138264
      9920133Mar  1 20139716
      9920134Apr  1 20138549
      9920135May  1 20138144
      9920136Jun  1 20137917
      9920137Jul  1 20139585
      9920138Aug  1 20137426
      9920139Sep  1 20137877
      99201310Oct  1 20139707
      99201311Nov  1 20138925
      99201312Dec  1 201313709
      9920141Jan  1 201411183
      9920142Feb  1 20148518
      9920143Mar  1 201410545
      9920144Apr  1 20149582
      9920145May  1 201410278
      9920146Jun  1 20149330
      9920147Jul  1 201411366
      9920148Aug  1 20149161
      9920149Sep  1 201410651
      99201410Oct  1 201411331
      99201411Nov  1 201410624223246
      99201412Dec  1 201417958241204
      9920151Jan  1 201511431241777

       

      The last column (NmbrOfThingsPast2Years) is the total of NrOfThings for the past 24 months. So the NmbrOfThingsPast2Years for Nov 2014 is the sum of NrOfThings for nov 2014, oct 2014, sep 2014, uptil dec 2012...

      What I would like to do is calculating the last column in Qlikview script because the first 4 columns are also determined in the script.

      Is there someone who can help me out?

       

      Kind regards,

      Wietze Hoekzema

        • Re: Calculating totals over last months
          Friedrich Hofmann

          Hi Wietze,

           

          so it seems you need either a set_expression to determine the last 24 months and sum up that field for that period - or you can do it in the script - just calculate an additional field:

           

          - First you have to split out the nr. (day) from that FirstDay_field

          -> Then use the MaKEDATE() fct. to construct a date from that

          -> Use DATE([field], 'MMM-YY') to have a unique month_year combination

          - In addition you have to construct in the script that month_year for today (Jan-15)

          => Then you can construct another field:

               - If that month_year >= (today's month_year - 24), THEN just copy the Nr_field

               - otherwise, just fill in a 0

          => Then you have a field that you can simply sum up on the GUI.

           

          HTH

           

          Best regards,

           

          DataNibbler

            • Re: Calculating totals over last months

              Hey DataNibbler,

              Thanks for your very quick response! I'm sorry, but I dont see what you mean...

              This is the piece of script I have for the table above:

               

              History:

              LOAD

              [Indicator],

              Year(DateApplied) as Year,

              Month(DateApplied) as Month,

              makedate(Year(DateApplied),Month(DateApplied)) as FirstDayPeriod,

              sum(NrOfThings) as NrOfThings

              Resident TasksPerformed

              group by

              [Indicator],

              Year(DateApplied),

              Month(DateApplied),

              makedate(Year(DateApplied),Month(DateApplied))

               

              What, in your opinion, is my next step? How do I construct that extra field you mention above?

               

              Thanks in advance!