Range Sum

[varunreddy]

Hi All,

I am trying to calculate the sum using the prior number which is aggregated by CDE and DimensionA.

Now I am trying to calculate cumulative observations, which is

A Completeness 25000

A Validity           26000 (25000+1000)

A Accuracy        26500.(25000+1000+500)

I got this using the expression:

RangeSum(Above(Total aggr(sum([Ruleset Failing Records]),[Associated DQ Dimension]CDE Name]),0,RowNo()) .

When I select Accuracy I get 500, instead of 26500.

Can anyone cme across this situation?

Thanks in advance!

Message was edited by: Varun Reddy

[sunny_talwar]

I saw that... and I am glad it did

[varunreddy]

Hi Sunny,

I got another issue. I am trying to add new field Rule. I have added the source file to the original discussion. Can you please help?

I am expecting this:

Date	CDE	Rule	DQ Dimesnion	Total	Failed	RollingSum
4/30/2017	CDE1	Rule1_C	Completeness	28,000	25000	25000
4/30/2017	CDE1	Rule1_V	Validity	28,000	1000	26000
4/30/2017	CDE1	Rule1_A	Accuracy	28,000	500	26500
4/30/2017	CDE2	Rule1	Completeness	28,000	25000	25000
4/30/2017	CDE2	Rule2	Validity	28,000	1000	26000
4/30/2017	CDE2	Rule3	Accuracy	28,000	500	26500
4/30/2017	CDE3	C_Rule1	Completeness	28,000	25000	25000
4/30/2017	CDE3	V_Rule2	Validity	28,000	1000	26000
4/30/2017	CDE3	A_Rule3	Accuracy	28,000	500	26500

[sunny_talwar]

checking..

[sunny_talwar]

Have you attached an Excel file or qvw?

[varunreddy]

Excel. I now added QVW

[sunny_talwar]

You can try this

Aggr(RangeSum(Above(Sum({<[DQ Dimesnion],Rule>}Failed),0,RowNo(TOTAL))), Date, CDE, [DQ Dimesnion])

but this will depend on the sorting of your DQ Dimesnion field in the script. So, although it works in the sample, it may or may not work for your case

[varunreddy]

Perfect

Many Thanks,

Varun

[sunny_talwar]

Remember this may or may not work for your actual issue because of the sorting issue....

[varunreddy]

Do you mean filtering of dimension?

[sunny_talwar]

No that's not what I meant.... Aggr() function depends on the load order of your field... Since we are doing a RangeSum within a Aggr(), the load order of your field DQ Dimension will determine how the chart will accumulate.