Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

wayneantinore-a
New Contributor III

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
MVP
MVP

Re: RangeSum across dimension

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)))

7 Replies
MVP
MVP

Re: RangeSum across dimension

May be this?

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

wayneantinore-a
New Contributor III

Re: RangeSum across dimension

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

MVP
MVP

Re: RangeSum across dimension

What is the new sorted field called?

wayneantinore-a
New Contributor III

Re: RangeSum across dimension

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

MVP
MVP

Re: RangeSum across dimension

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)))

wayneantinore-a
New Contributor III

Re: RangeSum across dimension

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

MVP
MVP

Re: RangeSum across dimension

You can read about that here:

The sortable Aggr function is finally here!