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!

[Anil_Babu_Samineni]

Read this? May be helps to you

Buckets

[mrthomasshelby]

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!

[Anil_Babu_Samineni]

Would you provide sample data?

[mrthomasshelby]

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

[sunny_talwar · Accepted solution]

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;

[Anil_Babu_Samineni]

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

[sunny_talwar]

There a secret sauce

[mrthomasshelby]

You are the best! Thanks a lot!

[mrthomasshelby]

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!