Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
)
anyone has any ideas? please?
What would you expect to see if a selection is made - AttCal_Period -> Dec 2013 , one row or 12 rows ?
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
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)
)
Hi Derrell,
This can also be done in script , if required.
PFA.
Regards,
Srashti
Thank you, Tresesco!
This is exactly what i needed.
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