Create Potential Buckets as Calculated Dimension

[mrthomasshelby]

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:

Vendor	Sales	Type	Type1	No.Of Alternates	Score1	Score2	Score3
T	531.88	C	Inter	357	1	3	1
L	405	C	Trade	252	1	3	1
F	110.5	C	Inter	47	1	3	1
V	5101.6	NC	Inter	1778	1	3	1
U	45.35	NC	Inter	84	1	3	1
U	30.43	NC	Inter	84	1	3	1
L	196	C	Inter	425	1	3	1
F	91.77	C	Inter	107	1	3	1
D	103.5	NC	Inter	83	1	3	1
S	594	NC	Inter	53	1	3	1
I	2470.29	C	Inter	6	1	2	1
N	45	NC	Inter	121	1	3	1
Q	120	NC	Inter	379	1	3	1
X	439.84	NC	Inter	4	1	1	1
H	78.72	C	Inter	495	1	3	1
H	7924.44	NC	Inter	38	1	3	1
M	430.5	C	Inter	15	1	3	1
L	2919.24	NC	Inter	217	1	3	1
L	2229.74	NC	Inter	217	1	3	1
C	231777	C	Inter	99	1	3	1
A	249.24	NC	Inter	293	1	3	1
F	96	C	Inter	22	1	3	1
E	1831.8	NC	Trade	10	1	2	1
G	63.42	NC	Trade	9	1	2	1
T	86.11	C	Inter	357	1	3	1
T	142.04999	C	Inter	357	1	3	1
H	197.22	C	Inter	318	1	3	1
H	18.38	C	Inter	318	1	3	1
H	33	C	Inter	318	1	3	1
D	20.96	C	Inter	436	1	3	1
D	18.9	C	Inter	436	1	3	1
B	13.85	NC	Inter	253	1	3	1
B	110.3	NC	Inter	253	1	3	1
B	1.52	NC	Inter	2	1	1	1
B	163.96	NC	Inter	253	1	3	1
B	2	NC	Inter	1	1	1	1
B	0.98	NC	Inter	2	1	1	1
B	65.78	NC	Trade	253	1	3	1
W	82746	NC	Trade	383	1	3	1
J	48.99	NC	Trade	67	1	3	1
P	78.61	C	Inter	416	1	3	1
P	82.83	C	Inter	416	1	3	1
P	95.42	C	Inter	416	1	3	1
P	36	C	Inter	416	1	3	1
O	496.80002	NC	Inter	83	1	3	1
O	900	NC	Inter	83	1	3	1
K	91037.24	C	Inter	1205	1	3	1
R	160	NC	Inter	1778	1	3	1
I	1750	C	Inter	1778	1	3	1
I	1950	C	Inter	1778	1	3	1

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!

[sunny_talwar]

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]

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]

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!

[sunny_talwar · Accepted solution]

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]

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]

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!

[sunny_talwar]

In your chart?

[mrthomasshelby]

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.

[sunny_talwar]

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?