topic Re: Rangesum with multiple dimensions in QlikView

Rangesum with multiple dimensions

varunreddy — Wed, 07 Jun 2017 00:06:45 GMT

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,

Message was edited by: Varun Reddy

Re: Rangesum with multiple dimensions

Anil_Babu_Samineni — Wed, 07 Jun 2017 01:50:28 GMT

Where this is not working? Can you describe little more

Re: Rangesum with multiple dimensions

varunreddy — Wed, 07 Jun 2017 02:06:39 GMT

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

Re: Rangesum with multiple dimensions

Anil_Babu_Samineni — Wed, 07 Jun 2017 02:12:59 GMT

Then try this?

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

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

Re: Rangesum with multiple dimensions

varunreddy — Wed, 07 Jun 2017 02:28:39 GMT

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

Re: Rangesum with multiple dimensions

Anil_Babu_Samineni — Wed, 07 Jun 2017 02:32:02 GMT

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)

Re: Rangesum with multiple dimensions

varunreddy — Wed, 07 Jun 2017 02:36:56 GMT

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

Re: Rangesum with multiple dimensions

varunreddy — Wed, 07 Jun 2017 02:38:34 GMT

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

Re: Rangesum with multiple dimensions

Anil_Babu_Samineni — Wed, 07 Jun 2017 02:39:34 GMT

Please share the data which you are using like

Date, CDE, Dimension, Failed

Re: Rangesum with multiple dimensions

varunreddy — Wed, 07 Jun 2017 02:42:08 GMT

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

Re: Rangesum with multiple dimensions

Anil_Babu_Samineni — Wed, 07 Jun 2017 02:57:15 GMT

From that data, Is this you are expecting?

Re: Rangesum with multiple dimensions

varunreddy — Wed, 07 Jun 2017 03:00:10 GMT

I am expecting this:

Date	Driver	Country	CDE	Dimension	RangeSum
4/1/2017	BCBS	US	CDE1	Completeness	25000
4/1/2017	BCBS	US	CDE1	Validity	26000
4/1/2017	BCBS	US	CDE1	Accuracy	26500
4/1/2017	BCBS	US	CDE2	Completeness	25000
4/1/2017	BCBS	US	CDE2	Validity	26000
4/1/2017	BCBS	US	CDE2	Accuracy	26500

Re: Rangesum with multiple dimensions

Anil_Babu_Samineni — Wed, 07 Jun 2017 03:02:38 GMT

This?

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

Re: Rangesum with multiple dimensions

varunreddy — Wed, 07 Jun 2017 03:04:47 GMT

Thanks Anil. Can you also share the previous expression,

Cheers,

Re: Rangesum with multiple dimensions

Anil_Babu_Samineni — Wed, 07 Jun 2017 03:05:25 GMT

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