Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wayne-a
Creator
Creator

RangeSum across dimension

Hi, I'm using the rangesum function to show a cumulative percentage across 4 values by date as shown below.  I use the following expression to do this:  Rangesum(above(Count([Order#])/ Aggr(Nodistinct count([Order#]),WeekStart),0,RowNo()))

RangeSumWorks.JPG

Now I'd like to flip the dimensions 'Week' and 'When Resulted' so I change the Aggr function to aggregate on 'When Resulted' like this: Rangesum(above(Count([Order#])/ Aggr(Nodistinct count([Order#]),[When Verified]),0,RowNo()))

That results in a chart that looks like the following:

RangeSumNotWorking.JPG

I understand why it's doing what it's doing but what I really want is for it to show the values in the first chart so I can see all the 'Before 4' values together etc.  I'm looking to have 'Before 4' have values of 70.77%, 73.39%, 75.55% etc.  Not sure if this can even be done. 

Thanks in advance for any help.

1 Solution

Accepted Solutions
sunny_talwar

Try this

Aggr(RangeSum(Above(Count([Order#])/ Count(TOTAL <WeekStart> [Order#]), 0, RowNo())), Week, VerifiedSort)

or this

Aggr(RangeSum(Above(Count([Order#])/ Count(TOTAL <WeekStart> [Order#]), 0, RowNo())), Week, (VerifiedSort, (NUMERIC)))

View solution in original post

7 Replies
sunny_talwar

May be this?

Aggr(RangeSum(Above(Count([Order#])/ Count(TOTAL <WeekStart> [Order#]), 0, RowNo())), Week, [When Resulted])

wayne-a
Creator
Creator
Author

Hi Sunny, thank you for your help again.  It's very close.  See below.... not sure why it's calculating the one dimension value out of order.  I load the data with another field for the sort order of the dimensions and sort by expression in the chart.  It's like the expression isn't taking that into account?

The current expression is:  Aggr(RangeSum(Above(Count([Order#])/ Count(TOTAL <WeekStart> [Order#]), 0, RowNo())), WeekStart, [When Verified])

RangeSumPartial.JPG

sunny_talwar

What is the new sorted field called?

wayne-a
Creator
Creator
Author

Hi Sunny, thank you for the reply and for taking your time to help.  It is called "VerifiedSort".  It is a number 0 through 4.  The "Bad Data" is normally filtered out.

WhenVerified.JPG

When I don't exclude it there is also has a strange result with the "Bad Data" value at 100% for any week that had any.

RangeSumNofilter.JPG

sunny_talwar

Try this

Aggr(RangeSum(Above(Count([Order#])/ Count(TOTAL <WeekStart> [Order#]), 0, RowNo())), Week, VerifiedSort)

or this

Aggr(RangeSum(Above(Count([Order#])/ Count(TOTAL <WeekStart> [Order#]), 0, RowNo())), Week, (VerifiedSort, (NUMERIC)))

wayne-a
Creator
Creator
Author

Sunny you are awesome!!! Thank you so much.  It was the second expression with the NUMERIC that did it.  Now I have to study it to understand it.   Thanks again.

RangeSumWorksNew.JPG

sunny_talwar

You can read about that here:

The sortable Aggr function is finally here!