topic Re: Create Potential Buckets as Calculated Dimension in App Development

Create Potential Buckets as Calculated Dimension

mrthomasshelby — Tue, 30 May 2017 11:20:01 GMT

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!

Re: Create Potential Buckets as Calculated Dimension

sunny_talwar — Tue, 30 May 2017 11:34:24 GMT

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.

Re: Create Potential Buckets as Calculated Dimension

mrthomasshelby — Tue, 30 May 2017 12:14:36 GMT

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.

Re: Create Potential Buckets as Calculated Dimension

mrthomasshelby — Wed, 31 May 2017 07:28:45 GMT

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!

Re: Create Potential Buckets as Calculated Dimension

sunny_talwar — Wed, 31 May 2017 15:25:44 GMT

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)))

Re: Create Potential Buckets as Calculated Dimension

mrthomasshelby — Thu, 01 Jun 2017 06:18:53 GMT

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

Re: Create Potential Buckets as Calculated Dimension

mrthomasshelby — Thu, 01 Jun 2017 10:08:17 GMT

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!

Re: Create Potential Buckets as Calculated Dimension

sunny_talwar — Thu, 01 Jun 2017 10:59:48 GMT

In your chart?

Re: Create Potential Buckets as Calculated Dimension

mrthomasshelby — Thu, 01 Jun 2017 11:22:47 GMT

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.

Re: Create Potential Buckets as Calculated Dimension

sunny_talwar — Thu, 01 Jun 2017 11:35:23 GMT

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?

Re: Create Potential Buckets as Calculated Dimension

mrthomasshelby — Thu, 01 Jun 2017 11:37:53 GMT

This is another chart where I want to display the Sales of only those Vendors with Potential as lets say 'Medium'.

Re: Create Potential Buckets as Calculated Dimension

sunny_talwar — Thu, 01 Jun 2017 11:43:31 GMT

Would you be able to share the chart you are working with so that I can just make the changes in the table you already have?

Re: Create Potential Buckets as Calculated Dimension

mrthomasshelby — Thu, 01 Jun 2017 11:48:15 GMT

Here's the qvf.TIA!

Re: Create Potential Buckets as Calculated Dimension

sunny_talwar — Thu, 01 Jun 2017 11:55:44 GMT

How about this?

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

(if(if(Aggr(

(Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo()))<=0.8),

(Vendor,(=Sum({<Vendor>} Sales),Desc))

),Score+1,

if(Aggr(

(Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo()))<=0.95),

(Vendor,(=Sum({<Vendor>} Sales),Desc))

),Score+2,

Score+3))<=7,'Low',

if(if(Aggr(

(Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo()))<=0.8),

(Vendor,(=Sum({<Vendor>} Sales),Desc))

),Score+1,

if(Aggr(

(Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo()))<=0.95),

(Vendor,(=Sum({<Vendor>} Sales),Desc))

),Score+2,

Score+3))<=10,'Medium','High')))) = 'Medium', Sum(Sales))

Re: Create Potential Buckets as Calculated Dimension

mrthomasshelby — Thu, 01 Jun 2017 12:00:07 GMT

Yes. I guess we'll have to go with nested ifs here. Set analysis doesn't work apparently. Also, I have another doubt. In the chart where it shows the No.of Vendors by Potential, Low has 16 Vendors and Medium has 11 Vendors. That means there should be 27 vendors. But in my data there are only 24 Vendors. How is this happening? I only want to categorize these 24 distinct Vendors. Is there some change that needs to be done?

Re: Create Potential Buckets as Calculated Dimension

sunny_talwar — Thu, 01 Jun 2017 12:07:05 GMT

Try this as your calculated dimension

=Aggr(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'))),

(Vendor,(=Sum({<Vendor>} Sales),Desc)))

Re: Create Potential Buckets as Calculated Dimension

mrthomasshelby — Thu, 01 Jun 2017 12:11:21 GMT

Thanks a lot! This works!

Re: Create Potential Buckets as Calculated Dimension

sunny_talwar — Thu, 01 Jun 2017 12:13:18 GMT

Super

Re: Create Potential Buckets as Calculated Dimension

mrthomasshelby — Thu, 01 Jun 2017 13:15:36 GMT

Hi! One last thing. I'm trying to show the Sales of Vendors of two Potentials, 'Low' and 'Medium'. I have tried summing their expressions but it doesn't work apparently. Could you please make any needed changes to achieve this in the attached qvf?

TIA!

Re: Create Potential Buckets as Calculated Dimension

sunny_talwar — Thu, 01 Jun 2017 13:39:59 GMT

May be this

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

(if(if(Aggr(

(Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo()))<=0.8),

(Vendor,(=Sum({<Vendor>} Sales),Desc))

),Score+1,

if(Aggr(

(Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo()))<=0.95),

(Vendor,(=Sum({<Vendor>} Sales),Desc))

),Score+2,

Score+3))<=7,'Low',

if(if(Aggr(

(Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo()))<=0.8),

(Vendor,(=Sum({<Vendor>} Sales),Desc))

),Score+1,

if(Aggr(

(Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo()))<=0.95),

(Vendor,(=Sum({<Vendor>} Sales),Desc))

),Score+2,

Score+3))<=10,'Medium','High')))), 'Medium', 'Low'), Sum(Sales))