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

# Calculating totals over last months

Hi all,

I have a table with dates and numbers like this:

 Indicator Year Month FirstDayPeriod NrOfThings NmbrOfThingsPast2Years 99 2013 1 Jan  1 2013 10858 99 2013 2 Feb  1 2013 8264 99 2013 3 Mar  1 2013 9716 99 2013 4 Apr  1 2013 8549 99 2013 5 May  1 2013 8144 99 2013 6 Jun  1 2013 7917 99 2013 7 Jul  1 2013 9585 99 2013 8 Aug  1 2013 7426 99 2013 9 Sep  1 2013 7877 99 2013 10 Oct  1 2013 9707 99 2013 11 Nov  1 2013 8925 99 2013 12 Dec  1 2013 13709 99 2014 1 Jan  1 2014 11183 99 2014 2 Feb  1 2014 8518 99 2014 3 Mar  1 2014 10545 99 2014 4 Apr  1 2014 9582 99 2014 5 May  1 2014 10278 99 2014 6 Jun  1 2014 9330 99 2014 7 Jul  1 2014 11366 99 2014 8 Aug  1 2014 9161 99 2014 9 Sep  1 2014 10651 99 2014 10 Oct  1 2014 11331 99 2014 11 Nov  1 2014 10624 223246 99 2014 12 Dec  1 2014 17958 241204 99 2015 1 Jan  1 2015 11431 241777

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

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:

[Indicator],

Year(DateApplied) as Year,

Month(DateApplied) as Month,

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

sum(NrOfThings) as NrOfThings

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?