Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
S
A small correction.
The column 12M Profit is not available in the original file and cannot be used (unfortunately)
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)
)
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.