Rangesum with multiple dimensions

[varunreddy]

Hi All,

I  am new to the RangeSum. I am trying to do the rolling sum with "Failed" records with multiple dimensions. I see that rangesum functionality is not working. I am attaching the Source file and the QVW.

Thanks in advance!

Cheers,

VK

Message was edited by: Varun Reddy

[Anil_Babu_Samineni]

Where this is not working? Can you describe little more

[varunreddy]

Hi Anil,

I was trying to Roll up Failed Records. I would like to see this:

Date        CDE       Dimension      Total       Failed    Rolling Sum

4/1/2017  CDE1  Completeness   28000      25000      25000

4/1/2017  CDE1   Validity             28000      1000        26000

4/1/2017  CDE1   Accuracy          28000       500        26500

But, I see this

Date        CDE       Dimension      Total       Failed    Rolling Sum

4/1/2017  CDE1  Completeness   28000      25000      25000

4/1/2017  CDE1   Validity             28000      1000        1000

4/1/2017  CDE1   Accuracy          28000       500        500

[Anil_Babu_Samineni]

Then try this?

Aggr(Rangesum(Below(Sum(Failed),0),Above(Failed,1,RowNo(TOTAL))),Dimension)

OR

Aggr(RangeSum(Above(Sum({<Dimension, Date= {'$(=vDate)'}>}Failed),0,RowNo())), Dimension)

[varunreddy]

Anil,

First isn't working.

Second is working but if we have multiple CDE's (i.e. CDE1 and CDE2) for same date, then it is not showing second CDE.

In the below scenario, we get this:

4/1/2017  Completeness CDE1  50000

4/1/2017  Validity           CDE1   52000

4/1/2017   Accuracy       CDE1   53000

Instead of:

4/1/2017  Completeness CDE1  25000

4/1/2017  Validity           CDE1   26000

4/1/2017   Accuracy       CDE1   26500

4/1/2017  Completeness CDE2   25000

4/1/2017  Validity           CDE2   26000

4/1/2017   Accuracy       CDE2   26500.

If I aggregate with CDE and Dimension, then we get

4/1/2017  Completeness CDE1  25000

4/1/2017  Validity           CDE1   1000

4/1/2017   Accuracy       CDE1   500

4/1/2017  Completeness CDE2   25000

4/1/2017  Validity           CDE2   1000

4/1/2017   Accuracy       CDE2   500.

4/1/2017	Completeness	CDE1	28000	25000	BCBS	US	Rule1_C
4/1/2017	Validity	CDE1	28000	1000	BCBS	US	Rule1_V
4/1/2017	Accuracy	CDE1	28000	500	BCBS	US	Rule1_A
4/1/2017	Completeness	CDE2	28000	25000	BCBS	US	Rule2_C
4/1/2017	Validity	CDE2	28000	1000	BCBS	US	Rule2_V
4/1/2017	Accuracy	CDE2	28000	500	BCBS	US	Rule2_A I

[Anil_Babu_Samineni]

Seems, 1st one also working to me

And for your question, you have to aggregate each together like below

Aggr(RangeSum(Above(Sum({<Dimension, Date= {'$(=vDate)'}>}Failed),0,RowNo())), Dimension, CDE)

[varunreddy]

Using first expression:

Date	CDE	Dimension	Aggr(Rangesum(Below(Sum(Failed),0),Above(Failed,1,RowNo(TOTAL))),Dimension)
			-
4/1/2017	CDE1	Completeness	75000
4/1/2017	CDE1	Validity	28000
4/1/2017	CDE1	Accuracy	27500

Using Second Expression:

Date	CDE	Dimension	Aggr(RangeSum(Above(Sum({<Dimension, Date= {'4/1/2017'}>}Failed),0,RowNo())), Dimension, CDE)
			-
4/1/2017	CDE1	Completeness	25000
4/1/2017	CDE1	Validity	1000
4/1/2017	CDE1	Accuracy	500
4/1/2017	CDE2	Completeness	50000
4/1/2017	CDE2	Validity	2000
4/1/2017	CDE2	Accuracy	1000

DataSource:

4/1/2017	Completeness	CDE1	28000	25000	BCBS	US	Rule1_C
4/1/2017	Validity	CDE1	28000	1000	BCBS	US	Rule1_V
4/1/2017	Accuracy	CDE1	28000	500	BCBS	US	Rule1_A
4/1/2017	Completeness	CDE2	28000	25000	BCBS	US	Rule2_C
4/1/2017	Validity	CDE2	28000	1000	BCBS	US	Rule2_V
4/1/2017	Accuracy	CDE2	28000	500	BCBS	US	Rule2_A

[varunreddy]

Previous data set has one CDE, but this set of data is having two CDE's for same date

[Anil_Babu_Samineni]

Please share the data which you are using like

Date, CDE, Dimension, Failed

[varunreddy]

Attached the Source file to the Original discussion. Please take a look