topic Re: Create Pareto Bins in Load Script in App Development

Create Pareto Bins in Load Script

mrthomasshelby — Fri, 19 May 2017 17:20:24 GMT

Hello,

Is there a way to create Pareto bins (say Top 80%,80-95% and Bottom 5%) in the load script?Say my data has Vendor Name and Sales. I'd like to create the above mentioned bins for this data. If you could you please explain with the help of an example. I desperately need help! TIA!

stalwar1‌ vinieme12‌. Please help!

Re: Create Pareto Bins in Load Script

Anil_Babu_Samineni — Fri, 19 May 2017 17:27:52 GMT

Read this? May be helps to you

Buckets

Re: Create Pareto Bins in Load Script

mrthomasshelby — Fri, 19 May 2017 17:34:55 GMT

I have read that already but as I'm fairly new to Qlik Sense and don't know the commands used to create Pareto bins in the load script, it'd be helpful if you could give me a detailed recipe to create such bins in the load script. Thanks!

Re: Create Pareto Bins in Load Script

Anil_Babu_Samineni — Fri, 19 May 2017 17:37:10 GMT

Would you provide sample data?

Re: Create Pareto Bins in Load Script

mrthomasshelby — Fri, 19 May 2017 17:41:50 GMT

Thanks for taking the time!

Let's say I have the following data. I'd like to classify them into 3 Pareto bins: Top 80%, 80-95%,Bottom 5%

Vendor Sales

A 1300

B 2200

A 1400

B 2100

C 2500

A 300

C 900

D 3200

B 2000

D 1500

E 1100

F 3500

E 1500

G 1400

H 600

H 900

G 1200

Re: Create Pareto Bins in Load Script

sunny_talwar — Fri, 19 May 2017 18:41:52 GMT

May be this

Table:

LOAD * INLINE [

Vendor, Sales

A, 1300

B, 2200

A, 1400

B, 2100

C, 2500

A, 300

C, 900

D, 3200

B, 2000

D, 1500

E, 1100

F, 3500

E, 1500

G, 1400

H, 600

H, 900

G, 1200

];

Left Join (Table)

LOAD Sum(Sales) as TotalSales

Resident Table;

TempTable:

LOAD Vendor,

Sum(Sales)/TotalSales as SalesByVendor

Resident Table

Group By Vendor, TotalSales;

Left Join (Table)

LOAD Vendor,

RangeSum(Peek('CumSalesByVendor'), SalesByVendor) as CumSalesByVendor,

If(RangeSum(Peek('CumSalesByVendor'), SalesByVendor) <= 0.80, Dual('Top 80%', 1),

If(RangeSum(Peek('CumSalesByVendor'), SalesByVendor) <= 0.95, Dual('80-95%', 2), Dual('Bottom 5%', 3))) as Bucket

Resident TempTable

Order By SalesByVendor desc;

DROP Table TempTable;

Re: Create Pareto Bins in Load Script

Anil_Babu_Samineni — Fri, 19 May 2017 18:51:53 GMT

You are the boss, How come you handle all stuff with in the Time only. I am trying same.

Re: Create Pareto Bins in Load Script

sunny_talwar — Fri, 19 May 2017 18:59:32 GMT

There a secret sauce

Re: Create Pareto Bins in Load Script

mrthomasshelby — Fri, 19 May 2017 19:07:39 GMT

You are the best! Thanks a lot!

Re: Create Pareto Bins in Load Script

mrthomasshelby — Fri, 19 May 2017 19:19:28 GMT

I also have a small clarification if you don't mind. Now let's say I have another field: Type. The data is now as follows:

Vendor Sales Type

A 1300 C

B 2200 N

A 1400 N

B 2100 N

C 2500 C

A 300 N

C 900 C

D 3200 N

B 2000 C

D 1500 C

E 1100 N

F 3500 C

E 1500 C

G 1400 N

H 600 C

H 900 N

G 1200 C

Now I'd like to apply a filter on Type. Let's say I apply the filter as 'C'. Now will the buckets be created on fields which only have the type C or will it still be created on all the fields? If it is still on the overall data, how can I make it create buckets only on the fields which have type as 'C'? Is it possible at all?

Thanks a lot again! You are a life saver! Much much appreciated!

Re: Create Pareto Bins in Load Script

sunny_talwar — Fri, 19 May 2017 19:20:55 GMT

Anything done in the script will be static on the front end... To create the same thing on the front end, you will need thisRecipe for a Pareto Analysis – Revisited

Re: Create Pareto Bins in Load Script

mrthomasshelby — Fri, 19 May 2017 19:25:36 GMT

Thanks! But the problem is I still need to have the same buckets as my dimension and the Sales are the measures. Could you please give me any idea how I can achieve this? The buckets also need to be done on data selected by filters only as I mentioned above.

Thanks again!

Re: Create Pareto Bins in Load Script

sunny_talwar — Fri, 19 May 2017 19:28:27 GMT

Thanks! But the problem is I still need to have the same buckets as my dimension and the Sales are the measures. Could you please give me any idea how I can achieve this? The buckets also need to be done on data selected by filters only as I mentioned above.

Same buckets? regardless of selections buckets need to change or they don't need to change? Selection in type will change the Sum(Sales) and the sorting will change and Top 80%, 80-95%, and Bottom 5% will change. So, technically you should can see new buckets, but I am confused if you want to see new buckets based on selection in type or should it always show based on over sales?

Re: Create Pareto Bins in Load Script

mrthomasshelby — Fri, 19 May 2017 19:36:38 GMT

To make myself more clear, let's say we have the data as follows:

Vendor Sales Type

A 1300 C

B 2200 N

A 1400 N

B 2100 N

C 2500 C

A 300 N

C 900 C

D 3200 N

B 2000 C

D 1500 C

E 1100 N

F 3500 C

E 1500 C

G 1400 N

H 600 C

H 900 N

G 1200 C

Now I have created the Buckets: Top 80%,80-95% and Bottom 5%. Now the output is as follows:

A,B,C,D,F are Top 80%. E & G are 80-95%. H is Bottom 5%. Now I apply the filter on Type = C. The data set changes to:

Vendor Sales Type

A 1300 C

C 2500 C

C 900 C

B 2000 C

D 1500 C

F 3500 C

E 1500 C

H 600 C

G 1200 C

I would like to create the same Buckets on this data now: Top 80%, 80-95% and Bottom 5%. I have observed that applying such a filter still retains the original Buckets calculated on the entire dataset. What I want is, if filters are applied, the same buckets be calculated on this filtered dataset.

Thanks!

Re: Create Pareto Bins in Load Script

sunny_talwar — Fri, 19 May 2017 19:38:50 GMT

This is exactly the issue with doing something in the script... it won't change based on selection. To make it change based on selection, you will need to do this in the script...Recipe for a Pareto Analysis – Revisited

I will try to apply this for your sample....

Re: Create Pareto Bins in Load Script

mrthomasshelby — Fri, 19 May 2017 19:43:12 GMT

If you could do that I'd be really grateful! The dimension should be Buckets(Top80%, 80-95% and Bottom 5%) and the measure is Sales.In short, I'd like to show a bar chart with Buckets as dimension and Sales as the measure. Thanks for taking so much of your time for helping me out! Really really appreciate it!

Re: Create Pareto Bins in Load Script

sunny_talwar — Fri, 19 May 2017 19:49:38 GMT

Try the attached

Aggr(

If(Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} TOTAL Sales),0,RowNo()))<=0.8, Dual('Top 80%', 1),

If(Rangesum(Above(Sum({<Vendor>} Sales)/Sum({<Vendor>} total Sales),0,RowNo()))<=0.95, Dual('80-95%', 2),

Dual('Bottom 5%', 3))),

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

)

Re: Create Pareto Bins in Load Script

mrthomasshelby — Fri, 19 May 2017 19:57:18 GMT

Thanks!This works! But what I'd like to display is a bar chart with the Pareto bins: Top 80%, 80-95% and Bottom 5% as the dimension and the Sales as the measure. Is there any way to do this? Thanks a lot!

Re: Create Pareto Bins in Load Script

mrthomasshelby — Fri, 19 May 2017 20:03:32 GMT

I see you can just add the above mentioned expression as the dimension. Thanks a lot! This has been a great learning thread for me! Keep up the good work kind sir and keep helping out noobs like me! Much much appreciated!

Re: Create Pareto Bins in Load Script

mrthomasshelby — Thu, 13 Jul 2017 07:31:38 GMT

Hello. A small clarification here. Is there any way to aggregate and sort the data in the script and then create these bins in the front end on this aggregated and sorted list? This is because I'm using the above formula as my dimension in the front end and it's slowing up the app due to the data volume. So, is there any way I can just perform this aggregating and sorting in the script and then create the Pareto bins in the chart in the front end? I just want to do the sorting part in the script but define the buckets in the front end so that the buckets are dynamic and the app performance improves as the list is already sorted.TIA!

stalwar1‌