Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)))
May be this?
Aggr(RangeSum(Above(Count([Order#])/ Count(TOTAL <WeekStart> [Order#]), 0, RowNo())), Week, [When Resulted])
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])
What is the new sorted field called?
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.
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)))
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.
You can read about that here: