Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
This?
RangeSum(Above( Sum({<Date= {'$(=vDate)'}>}Failed),0,RowNo(TOTAL)))
Where this is not working? Can you describe little more
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
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)
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 |
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)
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 |
Previous data set has one CDE, but this set of data is having two CDE's for same date
Please share the data which you are using like
Date, CDE, Dimension, Failed
Attached the Source file to the Original discussion. Please take a look