topic Re: Range Sum in QlikView

Range Sum

varunreddy — Tue, 06 Jun 2017 18:43:47 GMT

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

Re: Range Sum

Anonymous — Tue, 06 Jun 2017 18:45:55 GMT

try:

if( not isnull(Dimension),

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

)

Re: Range Sum

varunreddy — Tue, 06 Jun 2017 18:49:05 GMT

Hi Robin,

Thanks or quick response. This is not working.

Re: Range Sum

Anonymous — Tue, 06 Jun 2017 18:52:19 GMT

is your dimension really called "Dimension" or is it just a label?

Re: Range Sum

varunreddy — Tue, 06 Jun 2017 18:54:03 GMT

Associated DQ Dimension

Re: Range Sum

Anonymous — Tue, 06 Jun 2017 18:55:08 GMT

then try:

if( not isnull([DQ Dimension]),

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

)

Re: Range Sum

sunny_talwar — Tue, 06 Jun 2017 18:57:36 GMT

May be this

RangeSum(Above(Sum({<[Associated DQ Dimension]>}[Ruleset Failing Records]), 0, RowNo()) * Avg(1)

or this

Aggr(RangeSum(Above(Sum({<[Associated DQ Dimension]>}[Ruleset Failing Records]), 0, RowNo()), [Associated DQ Dimension], [CDE Name])

Re: Range Sum

varunreddy — Tue, 06 Jun 2017 18:58:27 GMT

Robin,

I tested by replacing Dimension with [Associated DQ Dimension]. But it is not working.

Thanks,

Re: Range Sum

Anonymous — Tue, 06 Jun 2017 18:59:52 GMT

if Sunny's proposal doesn't work, please post a sample...

Re: Range Sum

varunreddy — Tue, 06 Jun 2017 19:11:30 GMT

Hi Sunny,

Thanks for your response. But it didn't work.

This is my data:

This is my o/p:

Expression USed:

RangeSum(Above(Total aggr(sum( Failed),[DQ Dimesnion],CDE),0,RowNo()))

Re: Range Sum

Anonymous — Tue, 06 Jun 2017 19:13:53 GMT

Preparing examples for Upload - Reduction and Data Scrambling

Re: Range Sum

sunny_talwar — Tue, 06 Jun 2017 19:15:55 GMT

I don't understand the point of using Aggr() function if you have both your Aggr() dimensions as the chart dimension? What exactly are you trying to do? Do you have more dimensions then the ones you are showing or what is the requirement?

Re: Range Sum

varunreddy — Tue, 06 Jun 2017 19:16:24 GMT

I don't see option to upload.

This is what I see. Can you navigate me, I will upload.

Re: Range Sum

sunny_talwar — Tue, 06 Jun 2017 19:16:58 GMT

Check here:

Uploading a Sample

Re: Range Sum

varunreddy — Tue, 06 Jun 2017 19:18:49 GMT

Sunny,

I don't have any other dimensions. I have added my QVW and source file. Please take a look.

Re: Range Sum

sunny_talwar — Tue, 06 Jun 2017 19:21:10 GMT

Check the attached

RangeSum(Above(Sum({<[DQ Dimesnion]>}Failed), 0, RowNo())) * Avg(1)

Re: Range Sum

varunreddy — Tue, 06 Jun 2017 19:23:40 GMT

Thanks for the response. But, It is showing 26500 for all the selections.

Re: Range Sum

sunny_talwar — Tue, 06 Jun 2017 19:26:34 GMT

You used a sort expression which needed to be fixed as well

Match(Only({1}[DQ Dimesnion]), 'Completeness', 'Validity', 'Accuracy')

Re: Range Sum

varunreddy — Tue, 06 Jun 2017 19:28:38 GMT

Thanks Sunny,

It's working

Cheers,

Re: Range Sum

varunreddy — Tue, 06 Jun 2017 19:29:31 GMT

Robin,

Thanks for your response

Cheers,