10 Replies Latest reply: Aug 11, 2017 12:13 PM by Saima SM

# Cumulative Calculation Error

Hi All,

I am using following formula to get cumulative over the period. I have MonthlySaving field and I want to get running sum each month or filter by any period (Quarter/Year from my calendar date).

RangeSum(Above(TOTAL Sum([VRMonthlySaving]), 0, RowNo(TOTAL))).

I tried it on my data it gave me 0's and the values started from mar-2017 but wrong. I am using derived Year/Month from date. It gives me calculation for MonthlySaving properly by month as shown in graph and table. But I am unable to get cumulative. Where I am wrong.

• ###### Re: Cumulative Calculation Error

try this:

RangeSum(Above(Sum(VRMonthlySaving),0,RowNo()))

• ###### Re: Cumulative Calculation Error

Hi,

Try

RANGESUM(ABOVE(TOTAL Sum(VRMonthlySaving),0, ROWNO(TOTAL)))

• ###### Re: Cumulative Calculation Error

Hi Andry,

I tried but it gave me results ( not desired one) like in below picture

• ###### Re: Cumulative Calculation Error

Is VRMonthlySavings an expression? Does it include a set analysis to ignore selections in certain fields? May be you need to ignore the same fields in this expression

RangeSum(Above(TOTAL Sum({<Field1, Field2, Field3>}[VRMonthlySaving]), 0, RowNo(TOTAL)))

• ###### Re: Cumulative Calculation Error

VRMonthlySaving is a calculated field in load script. and its working well as shown in chart for any period. I just want to have running sum for this. Thanks

• ###### Re: Cumulative Calculation Error

You only have a single dimension in your chart? Any selections?

• ###### Re: Cumulative Calculation Error

I have date dimension for this particular chart. I am trying with date (yearMonth) dimension. I have fiilter pan for all derived calendar date, i.e date parts like year/monty/quarter.

• ###### Re: Cumulative Calculation Error

Expression look right to me.... may be a sample might help find the issue

• ###### Re: Cumulative Calculation Error

can I inbox you sample data? I am not allowed to public data. However I found a surprise in my expression for VRMontlySaving. I produced result in excel and VRMontlySaving doen't get populated as a result of expression properly. But it gives me monthly result properly in chart as I showed you in clip. I think that's why it doesnot give me running sum properly. here is my expression. It seems due to null(empty cells) in data the expression don't give results as I saw in excel file. I am attaching

(Gast*CostGast)+(Respirology*CostRespirology)+(Neurology*CostNeurology)
+(Endocrinology*CostEndocrinology)+(Rheumatology*CostRheumatology)
+(Hepatology*CostHepatology)as VRMonthlySaving

FROM \$(VQVDPath)Reporting.qvd](qvd);
store Reporting into \$(VQVDPath)Reporting.csv(txt) ; //store in excel to see results

• ###### Re: Cumulative Calculation Error

Try this

aggr(RangeSum(Above(Sum(VRMonthlySaving),0,RowNo())),VRDate.Year)