Sum Rolling 12 months

[Anonymous]

Hi all,

I am stuck; i'm trying to create a script that will allow Qlik to sum 12 months of values for a rolling 12 months.

not sure if this make sense but the below image is what i am trying to achieve.

2013-01 => sum the last 12 months inclusive

2013-02 => sum the last 12 months inclusive....

the result i want to achieve is a rolling 12 month of the sum of the rolling 12 months.

this is a dynamic table/graph which should automatically sum any previous 12 months.

appreciate any help. not sure if i should be using rangesum or if there are any other better way to achieve this.

Thanks!

[Anonymous]

anyone has any ideas? please?

[tresesco]

What would you expect to see if a selection is made - AttCal_Period -> Dec 2013 , one row or 12 rows ?

[Anonymous]

Hi Tresesco,

Ideally i would like to show 12 rows if possible when a user select Dec 2013.

so it would show Jan 2013, Feb 2013....Dec 2013.

but if not, force user to select the months required perhaps.

regards

Derrell

[tresesco · Accepted solution]

May be like:

sum(

  {<AttCal_Period,AttritionDate={'>$(=Addmonths(max(AttritionDate),-12))<=$(=Date(max(AttCal_Period)))'}>}

  aggr(rangesum(above(total sum({<AttCal_Period=>}[# Revenue Amount]),0,12)),AttCal_Period)

    )

[#6]

Hi Derrell,

This can also be done in script , if required.

PFA.

Regards,

Srashti

[Anonymous]

Thank you, Tresesco!

This is exactly what i needed.

[Anonymous]

Hi Srashti,

thanks for this. the result does not really match the output i was looking for.

nevertheless, this is indeed a good option that i can try.

regards

Derrell