Create Pareto Bins in Load Script

[mrthomasshelby]

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!

[sunny_talwar]

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

[mrthomasshelby]

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!

[sunny_talwar]

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?

[mrthomasshelby]

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!

[sunny_talwar]

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

[mrthomasshelby]

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!

[sunny_talwar]

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

    )

[mrthomasshelby]

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!

[mrthomasshelby]

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!

[mrthomasshelby]

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‌