Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

2 Replies
datanibbler
Esteemed Contributor

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

Not applicable

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!

Community Browser