Getting Rank analysis using the fractile() function in a calculated dimension to work across time

dadumas — Wed, 09 Jan 2019 15:16:56 GMT

I cannot get Rank analysis using the fractile() function in a calculated dimension to compute across time periods.

I am trying to use the fractile() function in a calculated dimension to show regional sales by "Quartile"

Bottom 25% = Quartile 4, 25-50% = Quartile 3, 50- 75% = Quartile 2, >75% = Quartile 1

The below calculated dimension works when displaying charts that do not display the time period (2016, 2017, 2018).

=if(aggr(sum(Profit),Region,Period) <= fractile(TOTAL (aggr(sum(Profit),Region,Period)), .25),4,
if(aggr(sum(Profit),Region,Period) <= fractile(TOTAL (aggr(sum(Profit),Region,Period)), .50),3,
if(aggr(sum(Profit),Region,Period) <= fractile(TOTAL (aggr(sum(Profit),Region,Period)), .75),2,1)))

However, when displaying period in a pivot chart as a dimension, nulls display across certain time periods. If only selecting a single period, the pivots work fine.

I have attached a screenshot and the qlikview .qvw.

I also have a sheet which uses the rank() function in its charts. Rank() works fine across time. I assume that is because rank ()is a measure?

I put another post in the "new to qlikview" board, and will remove that one.

Re: Getting Rank analysis using the fractile() function in a calculated dimension to work across ti

dadumas — Fri, 11 Jan 2019 11:41:48 GMT

Being a SQL God :), I decided to code a stored proc to populate a [YTD quartile] (1,2,3,4) directly into the fact records. Since this now exists as part of the load, I can use it without being a calced dimension. Right now users are satisfied this exists at the region level. If they also want to see it at the salesperson level, I will have to add that logic to the stored proc. So this becomes a workaround.

topic Getting Rank analysis using the fractile() function in a calculated dimension to work across time in QlikView

Getting Rank analysis using the fractile() function in a calculated dimension to work across time

Re: Getting Rank analysis using the fractile() function in a calculated dimension to work across ti