topic Re: RangeSum across dimension in App Development

RangeSum across dimension

wayne-a — Wed, 02 Aug 2017 15:24:17 GMT

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

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:

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.

Re: RangeSum across dimension

sunny_talwar — Wed, 02 Aug 2017 17:16:55 GMT

May be this?

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

Re: RangeSum across dimension

wayne-a — Wed, 02 Aug 2017 18:30:06 GMT

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

Re: RangeSum across dimension

sunny_talwar — Wed, 02 Aug 2017 18:34:00 GMT

What is the new sorted field called?

Re: RangeSum across dimension

wayne-a — Wed, 02 Aug 2017 18:46:23 GMT

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.

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.

Re: RangeSum across dimension

sunny_talwar — Wed, 02 Aug 2017 18:49:56 GMT

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

Re: RangeSum across dimension

wayne-a — Wed, 02 Aug 2017 19:13:03 GMT

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.

Re: RangeSum across dimension

sunny_talwar — Wed, 02 Aug 2017 19:14:37 GMT

You can read about that here:

The sortable Aggr function is finally here!