Calculating totals over last months

[#1]

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

[datanibbler]

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

[#3]

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!