Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All, need your help in calculating accumulated month wise sum in Balance Sheet pivot.
Attached is the current table which is having correct data with below formula:
RangeSum(before(Total SUM(FCBAL), 0, ColumnNo(TOTAL)))
Now my requirement is user just want to see last 3 years figures, which is 2017,2018,2019.
If I filter Year field, accumulation for 2017 is not happening. So for 2017 column, data should be accumulated from 2011-2017 and so on. I need to do it in front end.
Any solution on this please?
May be like: RangeSum(before(Total SUM(FCBAL), 0, 3))
Now I'm able to get correct numbers for 3 years with set analysis in straight table:
sum({1<[BalSheet.Period]={">=$(vMinBalSheet)<=$(=max([BalSheet.Period]))"}>}FCBAL)/1000
*** vMinBalSheet=min(Total{1} [BalSheet.Period])
But now after applying filter, numbers are not right. I don't want to bypass the field as user is having option to choose monthyear.
Any idea?