topic Re: Calculation ABC in QlikView

Calculation ABC

Thu, 07 Apr 2016 10:17:26 GMT

Hello, everyone.

I have the table:

Data:

LOAD * Inline [

PERIOD_ID,        REGION,        DRUGS_ID,    V_UAH,     Q,        COMPANY

201601001,        103,        1001,        50,        1,        111

201601001,        103,        1002,        75,        1,        111

201601001,        103,        1003,        265,    1,        111

201601001,        104,        1001,        55,        1,        111

201601001,        104,        1002,        75,        1,        111

201601001,        105,        1001,        51,        1,        111

201601001,        105,        1002,        80,        1,        111

201601001,        105,        1003,        400,    1,        111

201601001,        105,        1004,        8,        1,        111

201601001,        105,        1005,        560,    1,        111

201601002,        103,        1001,        45,        1,        111

201601002,        103,        1002,        77,        1,        111

201601002,        103,        1010,        430,    1,        111

201601002,        103,        1011,        60,        1,        111

201601002,        104,        1001,        54,        1,        111

201601002,        104,        1002,        74,        1,        111

201601002,        104,        1013,        98,        1,        111

201601002,        105,        1001,        64,        1,        111

201601002,        105,        1014,        120,    1,        111

201601002,        105,        1015,        32,        1,        111

201601001,        103,        1001,        60,        1,        222

201601001,        103,        1002,        73,        1,        222

201601001,        103,        1003,        295,    1,        222

201601001,        104,        1001,        56,        1,        222

201601001,        104,        1002,        70,        1,        222

201601001,        105,        1001,        54,        1,        222

201601001,        105,        1002,        84,        1,        222

201601001,        105,        1003,        460,    1,        222

201601001,        105,        1004,        9,        1,        222

201601001,        105,        1005,        600,    1,        222

201601002,        103,        1001,        49,        1,        222

201601002,        103,        1002,        71,        1,        222

201601002,        103,        1010,        530,    1,        222

201601002,        103,        1011,        67,        1,        222

201601002,        104,        1001,        57,        1,        222

201601002,        104,        1002,        77,        1,        222

201601002,        104,        1013,        97,        1,        222

201601002,        105,        1001,        67,        1,        222

201601002,        105,        1014,        122,    1,        222

201601002,        105,        1015,        37,        1,        222

];

I need to calculate ABC in chart:

How do it?

P. s. I am sorry for my english.

Re: Calculation ABC

marcus_sommer — Thu, 07 Apr 2016 11:04:30 GMT

Have a look here: Recipe for an ABC Analysis

- Marcus

Re: Calculation ABC

sunny_talwar — Thu, 07 Apr 2016 11:07:48 GMT

What do you expect to see in those two columns?

Re: Calculation ABC

Kushal_Chawda — Thu, 07 Apr 2016 11:10:18 GMT

see this

Recipe for a Pareto Analysis

ABC Analysis to set and remember classification at runtime

Re: Calculation ABC

Thu, 07 Apr 2016 13:00:07 GMT

Thanks, Marcus Sommer and everyone

I saw these article, but i need to other ABC-calculate.

Please, will watch attachmented files QlikComun_ABC_01.xls, where i wrote right ABC.

How do it in QlikView i don't know.

Thank you in advance.

Re: Calculation ABC

sunny_talwar — Thu, 07 Apr 2016 14:52:27 GMT

Potential solution: Need modification in the script:

Data:

LOAD *,

AutoNumber(PERIOD_ID&'|'&DRUGS_ID&'|'&COMPANY) as SORT_TEMP;

LOAD * Inline [

PERIOD_ID, REGION, DRUGS_ID, V_UAH, Q, COMPANY

201601001, 103, 1001, 50, 1, 111

201601001, 103, 1002, 75, 1, 111

201601001, 103, 1003, 265, 1, 111

201601001, 104, 1001, 55, 1, 111

201601001, 104, 1002, 75, 1, 111

201601001, 105, 1001, 51, 1, 111

201601001, 105, 1002, 80, 1, 111

201601001, 105, 1003, 400, 1, 111

201601001, 105, 1004, 8, 1, 111

201601001, 105, 1005, 560, 1, 111

201601002, 103, 1001, 45, 1, 111

201601002, 103, 1002, 77, 1, 111

201601002, 103, 1010, 430, 1, 111

201601002, 103, 1011, 60, 1, 111

201601002, 104, 1001, 54, 1, 111

201601002, 104, 1002, 74, 1, 111

201601002, 104, 1013, 98, 1, 111

201601002, 105, 1001, 64, 1, 111

201601002, 105, 1014, 120, 1, 111

201601002, 105, 1015, 32, 1, 111

201601001, 103, 1001, 60, 1, 222

201601001, 103, 1002, 73, 1, 222

201601001, 103, 1003, 295, 1, 222

201601001, 104, 1001, 56, 1, 222

201601001, 104, 1002, 70, 1, 222

201601001, 105, 1001, 54, 1, 222

201601001, 105, 1002, 84, 1, 222

201601001, 105, 1003, 460, 1, 222

201601001, 105, 1004, 9, 1, 222

201601001, 105, 1005, 600, 1, 222

201601002, 103, 1001, 49, 1, 222

201601002, 103, 1002, 71, 1, 222

201601002, 103, 1010, 100, 1, 222

201601002, 103, 1011, 67, 1, 222

201601002, 104, 1001, 57, 1, 222

201601002, 104, 1002, 77, 1, 222

201601002, 104, 1013, 97, 1, 222

201601002, 105, 1001, 67, 1, 222

201601002, 105, 1014, 122, 1, 222

201601002, 105, 1015, 800, 1, 222

];

AggrTable:

LOAD PERIOD_ID,

DRUGS_ID,

COMPANY,

Sum(V_UAH) as Sum_V_UAH

Resident Data

Group By PERIOD_ID, DRUGS_ID, COMPANY;

Order:

LOAD AutoNumber(PERIOD_ID&'|'&DRUGS_ID&'|'&COMPANY) as SORT

Resident AggrTable

Order By PERIOD_ID, Sum_V_UAH desc;

DROP Table AggrTable;

FinalData:

LOAD PERIOD_ID,

SORT_TEMP as SORT,

REGION,

DRUGS_ID,

V_UAH,

COMPANY

Resident Data;

DROP Table Order, Data;

Pivot Table

Dimension: PERIOD_ID

DRUGS_ID

Expressions:

1) Sum ({$<COMPANY = {"111"}>} V_UAH)

2) Sum ({$<COMPANY = {"222"}>} V_UAH)

3) Aggr(RangeSum(Above(TOTAL Sum({$<COMPANY = {"111"}>} V_UAH)/Sum(TOTAL <PERIOD_ID> {$<COMPANY = {"111"}>} V_UAH), 0, RowNo())), SORT)

4) Aggr(RangeSum(Above(TOTAL Sum({$<COMPANY = {"222"}>} V_UAH)/Sum(TOTAL <PERIOD_ID> {$<COMPANY = {"222"}>} V_UAH), 0, RowNo())), SORT)

Re: Calculation ABC

Fri, 08 Apr 2016 08:07:20 GMT

Hello, Sunny T.

Thanks a lot for help but it isn't.

Re: Calculation ABC

sunny_talwar — Fri, 08 Apr 2016 16:30:07 GMT

It isn't? Not sure I understand?

Re: Calculation ABC

Sun, 10 Apr 2016 06:25:29 GMT

Hello, everyone.

I calculated ABC. Please, will watch attachmented files Test_ABC_02.qvw.

But the each times need press button "Recalc ABC":

Re: Calculation ABC

sunny_talwar — Sun, 10 Apr 2016 12:21:09 GMT

One way is to add triggers to your variables and assign the same actions you applied to the button object. I have added the pareto select action, not sure if the copy state content was important or not, but you can add it based on your requirement

Re: Calculation ABC

Mon, 11 Apr 2016 06:28:31 GMT

Hello, everyone.

This question is solved.

Thanks a lot everyony, thanks Sunny T, kushal chawda, Marcus Sommer !!!

Have a nice day.