topic Re: A complex rolling 12 month sum in QlikView

A complex rolling 12 month sum

Mon, 27 Apr 2015 19:27:11 GMT

Hello,

I can use the forum help with a complex rolling 12 month chart I am trying to create.

I have a list of accounts with different profitability during the months. I want to create a line chart that shows for each month the sum of profit for accounts that have a negative sum of profit in the last 12 months. I have tried using a few of the following formulas but none give the right result.

I think there is an issue with the “above” function when trying to use a conditional qualifier in it.

sum(if(aggr(sum([Profit]),[Account ID])<0, aggr(rangesum(above(sum({$<[Time]>}[Profit], [Account ID]),0,12)),[Country],[Time]))) provided nothing

sum(if(aggr(sum([Profit]),[Account ID])<0, aggr(rangesum(above(sum({$<[Time]>}[Profit]),0,12)),[Country],[Time])))

Provided one (wrong) data point for the first time period.

Once I have the expression right I want to present only the data for time periods where there are full 12 month (i.e. from December 01).

Unfortunately I can’t change the script. I’m attaching a mock qvw file and excel with the desired outcome in sheet #4

Many thanks,

Re: A complex rolling 12 month sum

Mon, 27 Apr 2015 20:11:47 GMT

A small correction.

The column 12M Profit is not available in the original file and cannot be used (unfortunately)

Re: A complex rolling 12 month sum

ramoncova06 — Mon, 27 Apr 2015 20:12:11 GMT

this expression works based on the max date

=if(sum( {< Time = {">=$(=(addmonths(max(Time),-12)))<=$(=(max(Time)))"} >}Profit) <0,

sum( {< Time = {">=$(=(addmonths(max(Time),-12)))<=$(=(max(Time)))"} >}Profit)

)

Re: A complex rolling 12 month sum

Tue, 28 Apr 2015 13:53:11 GMT

Thanks, but unfortunately this doesn't work well. It shows wrong data and skips a few months.

I will look into the addmonth/max combination but if someone can come up with a solution that would be great.