Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Quartile metric calculation using set analysis

Hi All,

I need to create a pivot table by grouping the metric (sales) into 4 groups (Quartiles).

Q1 - Sum of all sales where Sales > (Max(Sales)*0.75)

Q2 - Sum of all sales where Sales >= (Max(Sales)*0.5) and Sales < (Max(Sales)*0.75)

Q3 - Sum of all sales where Sales >= (Max(Sales)*0.25) and Sales < (Max(Sales)*0.5)

Q4 - Sum of all sales where Sales < (Max(Sales)*0.25)

e.g: For NAM and Dir combination Max(Sales) is 15 and Max(Sales)*0.75 is 11.25

Q1= 15+12 =27

Q2= 10

Q3=0

Q4=0

Data set

RegionTitleEmployeeSales
NAMDirE110
NAMDirE215
NAMDirE312
NAMVPE416
NAMVPE513
NAMVPE610
NAMVPE72
ASIAVPE810
ASIAVPE95
ASIAVPE101

Expected Output:

RegionTitleQ1Q2Q3Q4
NAMDir271000
NAMVP291002
ASIAVP10501

I tried below expression but does not produce correct results.

Q1= sum ({$<Sales={'>= $(#=max(Sales)*0.75)'}>}Sales)

Q2= sum ({$<Sales={'< $(#=max(Sales)*0.75) >= $(#=max(Sales)*0.5) '}>}Sales)

Q3= sum ({$<Sales={'< $(#=max(Sales)*0.5) >= $(#=max(Sales)*0.25) '}>}Sales)

Q4= sum ({$<Sales={'< $(#=max(Sales)*0.25)'}>}Sales)

Please advice.

1 Solution

Accepted Solutions
Highlighted
Creator II
Creator II

Try the following expressions:

Q1:

Sum(Aggr(If(Sum(Sales) >= Max(TOTAL <Title,Region> Sales) * 0.75,Sum(Sales)),Region,Title,Employee))

Q2:

Sum(Aggr(If(Sum(Sales) >= Max(TOTAL <Title,Region> Sales) * 0.5 And Sum(Sales) < Max(TOTAL <Title,Region> Sales) * 0.75,Sum(Sales)),Region,Title,Employee))

Q3 and Q4 same as these with the factors changed.  This gives the output your after.

It is also customisable so that if your dimensions changes, you can modify the functions to reflect in the appropriate places.

If you introduce other dimensions particularly if they reduce the lowest level below employee may need to play with the functions a little.

View solution in original post

16 Replies
Highlighted
MVP
MVP

Please check enclosed file.

My output is slightly different...

Let me know whether it works f

Highlighted
Partner
Partner

Hi,

I don't think you can use set analysis for this because the set analysis condition is only calculated once for the whole dataset, not per dimension. This means that Max(Sales) inside a set analysis will get the max (=16) for the whole dataset.

You may need to calculate those max values beforehand in the script.

-Teemu

Highlighted
Not applicable

Teemu,

Thanks for replying. Is there any other way to achieve this e.g. aggr(), fractlie()?

Highlighted
Not applicable

Hi Manish,

appreciate your help. But the xpressions does not appear to work for the reason Teemu has mentioned. I changed the input numbers slightly and reloaded qvw.  Check the output. They are wrong.

screenshot.PNG.png

Highlighted
Partner
Partner

Hi,

You may be able to use some sort of If statement with aggr etc. but I'm not sure. And personally I hate If statements because they can be very slow. If the row count is not an issue I would probably calculate those dimension specific max values in the script. It would make the expressions a bit simpler too I think.

Sorry but I can't provide you with an example at this time. I'll see if I have time later today.

-Teemu

Highlighted
Not applicable

I agree with Teemu, It is efficient to do it in Script.

Other dirty way is to create Variables, But you will end up with 4 * 5 * 3 variables

Assuming 4 regions, 5 Titles and 3 Quartiles. Use those variables in the expression.

It is interesting to know if some one solves this.

Highlighted
Not applicable

Sudheer, Teemu,

Script may not be an option as I have 30 other dimensions that can change the combination. For e.g. Month, Year, Dept, Region etc.  User can make selection on any of these dimensions and calculation needs to reflect the selections.

I shared a simplified data set for illustrating the problem.

Highlighted
Creator II
Creator II

Try the following expressions:

Q1:

Sum(Aggr(If(Sum(Sales) >= Max(TOTAL <Title,Region> Sales) * 0.75,Sum(Sales)),Region,Title,Employee))

Q2:

Sum(Aggr(If(Sum(Sales) >= Max(TOTAL <Title,Region> Sales) * 0.5 And Sum(Sales) < Max(TOTAL <Title,Region> Sales) * 0.75,Sum(Sales)),Region,Title,Employee))

Q3 and Q4 same as these with the factors changed.  This gives the output your after.

It is also customisable so that if your dimensions changes, you can modify the functions to reflect in the appropriate places.

If you introduce other dimensions particularly if they reduce the lowest level below employee may need to play with the functions a little.

View solution in original post

Highlighted
Not applicable

Bingo!! that works perfectly!

Thanks Michael.