10 Replies Latest reply: Mar 22, 2018 3:30 PM by William Wright

# How to create 4 straight table charts that seperate stores into quartiles based on GP%

Can I use the dimension limitation function to create 4 seperate straight table charts ranking stores based on their GP%'s with each straight table containing 16 stores? The first table would include highed 16, next would include 17-32, the next would include 33-48, and the last would include the bottom 16.

• ###### Re: How to create 4 straight table charts that seperate stores into quartiles based on GP%

if you are looking within a table you can use Class() for that

for a different table, you can use set analysis on a dimension

• ###### Re: How to create 4 straight table charts that seperate stores into quartiles based on GP%

Use the rank() function:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/RankingFunctions/rank.htm

Exp of use:

Table1: first 16

dimension: YourDim

Measure: sum({<YourDim={"=rank(sum({<YourDim>}YourMeasure),YourDim)<=16"}>}YourMeasure)

Table2: btwn 17-32

dimension: YourDim

Measure: sum({<YourDim={"=rank(sum({<YourDim>}YourMeasure),YourDim)>=17"}>

*

<YourDim={"=rank(sum({<YourDim>}YourMeasure),YourDim)<=17"}>

}YourMeasure)

Table3: btwn 33-48

dimension: YourDim

Measure: sum({<YourDim={"=rank(sum({<YourDim>}YourMeasure),YourDim)>=33"}>

*

<YourDim={"=rank(sum({<YourDim>}YourMeasure),YourDim)<=48"}>

}YourMeasure)

Table1: >48

dimension: YourDim

Measure: sum({<YourDim={"=rank(sum({<YourDim>}YourDim),YourDim)>48}>}YourMeasure)

• ###### Re: How to create 4 straight table charts that seperate stores into quartiles based on GP%

I used the dimension limitation to calc the top 16 and then tried the following code:

sum({<A277_DEAL_LOCN={"=rank(sum({<A277_DEAL_LOCN>}A277_DEAL_LOCN),A277_DEAL_LOCN)<=16"}>}vNewFSGP%YTD)

The second table didn't return any results.

Could this be because I'm using a variable as a measure?

• ###### Re: How to create 4 straight table charts that seperate stores into quartiles based on GP%

it's

sum({<A277_DEAL_LOCN={"=rank(sum({<A277_DEAL_LOCN>}vNewFSGP%YTD),A277_DEAL_LOCN)<=16"}>}vNewFSGP%YTD)

• ###### Re: How to create 4 straight table charts that seperate stores into quartiles based on GP%

I've entered the code as specified but I'm not returning any data in my table.

• ###### Re: How to create 4 straight table charts that seperate stores into quartiles based on GP%

It's working for me.

Can u post an image of what u've done and maybe the dimension and measures ure using?

• ###### Re: How to create 4 straight table charts that seperate stores into quartiles based on GP%

could it possibly be because I am using a variable I created to calc the GP% (vNewFSGPYTD)?

would this require a different syntax?

• ###### Re: How to create 4 straight table charts that seperate stores into quartiles based on GP%

Sorry, I'm having trouble uploading anything here.

This is what I've entered into the expression editor:

sum({<A277_DEAL_LOCN={"=rank(sum({<A277_DEAL_LOCN>}vGPYTD),A277_DEAL_LOCN)<=16"}>}vGPYTD)

This is the variable that I created as vGPYTD:

(Sum({\$<A027_DESIGNATION={"N"},A028_STATUS={"FS"},CFD_BMT_TYPE={"B"},A222_LOT_LOCN-={"DFS","NFS",105},A063_GL_POST_DATE.autoCalendar.YearQuarter={"2017-Q4","2018-Q1","2018-Q2","2018-Q3"}>}[ECALC_UNIT_REVENUE]) -Sum({\$<A027_DESIGNATION={"N"},A028_STATUS={"FS"},CFD_BMT_TYPE={"B"},A222_LOT_LOCN-={"DFS","NFS",105},A063_GL_POST_DATE.autoCalendar.YearQuarter={"2017-Q4","2018-Q1","2018-Q2","2018-Q3"}>}[A092_TOT_COST])) / Sum({\$<A027_DESIGNATION={"N"},A028_STATUS={"FS"},CFD_BMT_TYPE={"B"},A222_LOT_LOCN-={"DFS","NFS",105},A063_GL_POST_DATE.autoCalendar.YearQuarter={"2017-Q4","2018-Q1","2018-Q2","2018-Q3"}>}[ECALC_UNIT_REVENUE])

The variable works fine if I put it into the expression editor as \$(vGPYTD)

Could the variable I created be what is causing the data not to load?

Should it have a different syntax in the function you provided?

• ###### Re: How to create 4 straight table charts that seperate stores into quartiles based on GP%

If It does work, please close the thread by marking the correct answer as correct @william.wright

• ###### Re: How to create 4 straight table charts that seperate stores into quartiles based on GP%

It's not returning any data