Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
dadumas
Contributor II

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

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.

 

 

Tags (1)
1 Reply
dadumas
Contributor II

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

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.