Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

mrthomasshelby
Contributor

Create Potential Buckets as Calculated Dimension

Hello all,

I have a rather complicated set of rules to create a bucket which classifies Vendors as 'High','Medium' or 'Low'. I have the data as follows:

       

VendorSalesTypeType1No.Of AlternatesScore1Score2Score3
T531.88CInter357131
L405CTrade252131
F110.5CInter47131
V5101.6NCInter1778131
U45.35NCInter84131
U30.43NCInter84131
L196CInter425131
F91.77CInter107131
D103.5NCInter83131
S594NCInter53131
I2470.29CInter6121
N45NCInter121131
Q120NCInter379131
X439.84NCInter4111
H78.72CInter495131
H7924.44NCInter38131
M430.5CInter15131
L2919.24NCInter217131
L2229.74NCInter217131
C231777CInter99131
A249.24NCInter293131
F96CInter22131
E1831.8NCTrade10121
G63.42NCTrade9121
T86.11CInter357131
T142.04999CInter357131
H197.22CInter318131
H18.38CInter318131
H33CInter318131
D20.96CInter436131
D18.9CInter436131
B13.85NCInter253131
B110.3NCInter253131
B1.52NCInter2111
B163.96NCInter253131
B2NCInter1111
B0.98NCInter2111
B65.78NCTrade253131
W82746NCTrade383131
J48.99NCTrade67131
P78.61CInter416131
P82.83CInter416131
P95.42CInter416131
P36CInter416131
O496.80002NCInter83131
O900NCInter83131
K91037.24CInter1205131
R160NCInter1778131
I1750CInter1778131
I1950CInter1778131

Now what I'd like to do is as follows:

1.Perform a Pareto analysis on the above data and classify the Vendors into 3 classes: Top 80%,80-95% and Bottom 5%.

2.Based on the Vendor's Pareto class, I'd like to add another Score criteria, let's say Score 4. Score 4 should be allotted as follows:

  (a).If the Vendor falls in Top 80%, allot Score4 as 1.

  (b).If the Vendor falls in 80-95%, allot Score4 as 2.

  (c).If the Vendor falls in Bottom 5%, allot Score4 as 3.

3.After completing the Score4 column, now sum up all the Score Columns, Score1 through Score4. Let's call it Total Score.

4.If Total Score <=7, Bucket is 'Low'. If  7<Total Score<=10, Bucket is 'Medium'. If Total Score>10, Bucket is 'High'.

Now, there are some exceptions to the above rules as well:

(a). If Type='C', the Bucket should automatically be 'Low'.

(b).If Type1='Trade', the Bucket should automatically be 'Low'.

(c).If No.Of Alternates < 3, the Bucket should automatically be 'Low'.

In short if any one or more of the above criteria are met for the Vendors, the Bucket should be 'Low'.

Added to my complications, all this should be implemented in the front end, as a Calculated Dimension. This is way out of my little knowledge of Qlik Sense and I badly need help to implement this. I'd be grateful to anyone who'd hep me out in this! TIA!

stalwar1‌ Help me out kind sir!

1 Solution

Accepted Solutions
MVP
MVP

Re: Create Potential Buckets as Calculated Dimension

May be try this

Potential

If(Type = 'C' or Type1 = 'Trade' or [No.Of Alternates] < 3, 'Low',

If(

  RangeSum(Score,

  If(Aggr(NODISTINCT Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo())), (Vendor,(=Sum({<Vendor>} Sales),Desc))) <= 0.8, 1,

  If(Aggr(NODISTINCT Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo())), (Vendor,(=Sum({<Vendor>} Sales),Desc))) <= 0.95, 2, 3))) <= 7, 'Low',

If(

  RangeSum(Score,

  If(Aggr(NODISTINCT Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo())), (Vendor,(=Sum({<Vendor>} Sales),Desc))) <= 0.8, 1,

  If(Aggr(NODISTINCT Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo())), (Vendor,(=Sum({<Vendor>} Sales),Desc))) <= 0.95, 2, 3))) <= 10, 'Medium', 'High')))

Final Score

RangeSum(Score,

If(Aggr(NODISTINCT Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo())), (Vendor,(=Sum({<Vendor>} Sales),Desc))) <= 0.8, 1,

If(Aggr(NODISTINCT Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo())), (Vendor,(=Sum({<Vendor>} Sales),Desc))) <= 0.95, 2, 3)))

23 Replies
MVP
MVP

Re: Create Potential Buckets as Calculated Dimension

Encourage you to add a qvd file where you have tried something and we can take it forward. This suggestion is not just for this post, but any post you make in the future.

mrthomasshelby
Contributor

Re: Create Potential Buckets as Calculated Dimension

Hello. The only thing I know is to perform a Pareto analysis and here's a qvf where I did that. Please help me out in proceeding further.

mrthomasshelby
Contributor

Re: Create Potential Buckets as Calculated Dimension

Hello, so I have worked a bit on this further and succeeded partially. I have created the basic buckets as you can see. What I need help with now is incorporating those exceptions into the bucket. Could you please help me on how to do this? TIA!

MVP
MVP

Re: Create Potential Buckets as Calculated Dimension

May be try this

Potential

If(Type = 'C' or Type1 = 'Trade' or [No.Of Alternates] < 3, 'Low',

If(

  RangeSum(Score,

  If(Aggr(NODISTINCT Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo())), (Vendor,(=Sum({<Vendor>} Sales),Desc))) <= 0.8, 1,

  If(Aggr(NODISTINCT Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo())), (Vendor,(=Sum({<Vendor>} Sales),Desc))) <= 0.95, 2, 3))) <= 7, 'Low',

If(

  RangeSum(Score,

  If(Aggr(NODISTINCT Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo())), (Vendor,(=Sum({<Vendor>} Sales),Desc))) <= 0.8, 1,

  If(Aggr(NODISTINCT Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo())), (Vendor,(=Sum({<Vendor>} Sales),Desc))) <= 0.95, 2, 3))) <= 10, 'Medium', 'High')))

Final Score

RangeSum(Score,

If(Aggr(NODISTINCT Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo())), (Vendor,(=Sum({<Vendor>} Sales),Desc))) <= 0.8, 1,

If(Aggr(NODISTINCT Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo())), (Vendor,(=Sum({<Vendor>} Sales),Desc))) <= 0.95, 2, 3)))

mrthomasshelby
Contributor

Re: Create Potential Buckets as Calculated Dimension

Thanks! This seems much more efficient than the nested if loops I was using. Here's what I came up with as an alternative:

mrthomasshelby
Contributor

Re: Create Potential Buckets as Calculated Dimension

Hello! Is there anyway I could use this expression in a set analysis type? For example I just want to display the Sales by Vendors with only Potential='High'. How do I achieve this? TIA!

MVP
MVP

Re: Create Potential Buckets as Calculated Dimension

In your chart?

mrthomasshelby
Contributor

Re: Create Potential Buckets as Calculated Dimension

Yes, in a chart in the front end(not script). If it was in the script I could have created a dimension and used it directly in the set analysis.

MVP
MVP

Re: Create Potential Buckets as Calculated Dimension

Is it the same chart that was there in your sample? You want to see only those rows which are High or is this another chart?

Community Browser