Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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 Smiley Happy, 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.