If, Above, and Rangesum

[nswarts]

Creating a Sales dashboard and want to create a column that will show the accumulated sales but reset when we enter a new quarter in the year. 

If(Above(TOTAL QuarterYear) = QuarterYear,
rangesum(above(sum(sales), 0, RowNo())),
sum(sales)
)

If the QuarterYear field is the same as the above row, add them together until it reaches a new quarter where it will reset to just sum(sales) and continue on from there. 

Is it not possible to use it this way?

I've verified the IF statement is working (by changing the output to 1 if true and 2 if false), however it wont accumulate the sales for each day the rangesum will just return the same as sum sales. 

[Nicolae_Alecu]

Hello,

You can try it like this:

=rangesum(above(Sales,0,RowNo(Total)))

Best regards,

[nswarts]

Should've included the actual dimension in my original post

Unfortunately still getting the same results, just a repeat of sales and no accumulation

[pravinboniface · Accepted solution]

@nswarts I think you are using Sales as a dimension which may explain why the expression provided by @Nicolae_Alecu  did not work for you.  You could change the Sales to a measure (sum(sales)) or if you cannot do that, you can try the following:

aggr(rangesum(above(Sales,0,RowNo(Total))), QuarterYear,Date)

[nswarts]

Already had it as a measure (sum(sales)) just had labels written out for it, the aggregation to quarteryear and date did work though, thank you!