topic set analysis help for % in QlikView

set analysis help for %

Thu, 17 Nov 2011 08:01:26 GMT

hi all,

i have the data like

cat amt

A 10

B 20

C 30

D 40

E 50

i need to show the output as cat, amt, % of A in Cat A,C,D that is 10/10,30/10...

pivot table.

cat amt %of A

A 10 100%

B 20

C 30 300%

D 40 400%

E 50

dont use the variable. i tried using the following set but its not working bcoz of excluding the value.

thanks in advance.

set analysis help for %

ToniKautto — Thu, 17 Nov 2011 08:11:52 GMT

Please add the expression you are using, or even more preferrably add a sample QV.

set analysis help for %

Thu, 17 Nov 2011 08:17:41 GMT

thanks for your reply

the expression i used,

pick(match(Cat,'A','C','D'),Sum({<Cat=A}>}amt),Sum({<Cat=C}>}amt),Sum({<Cat=D}>}amt))/Sum({<Cat={A}>}amt)

Re: set analysis help for %

ToniKautto — Thu, 17 Nov 2011 08:47:44 GMT

I dont see where you are headed with that expression, so here comes a different approach as a suggested solution.

First, I would look for a way to identify the A value on each row. By calculating the sum of amt over the entire data set, with cat A selected I will get the sum of A's on each row in the straight table. See the expression sum(TOTAL {<cat={'A'}>} amt) the attached sample.

The second step is to simply take the amt value for the corresponding dimension and divid with A's amt value as amt / sum(TOTAL {<cat={'A'}>} amt)

Finally you would want to format the expression to present persentage.

Re: set analysis help for %

Thu, 17 Nov 2011 09:03:34 GMT

thank you so much for your reply,

sorry, i miss some points, i can use the above expression for summary report.

but wen i go for by company like, i cant use the total in the expression.

Cat Company X Y Z

Amt % Amt % Amt %

A 10 100 20 100 5 100

B 20 200 10 50 10 200

C 30 300 40 200 20 400

D 40 400 60 300 15 300

E 50 500 70 350 5 100

similarly by month also we cant use the total in the expr...

thanks in advance.

Re: set analysis help for %

ToniKautto — Thu, 17 Nov 2011 10:05:43 GMT

Aha, that explains the complexity. If I got your request right the main task is still to find the sum of A for each cell, so that you can divide the amt.

The first step is to find the sum of amt per company, which can be done by counting the sum per company with;

sum(TOTAL <COMPANY> amt)

After confirming that this is accurate the expression needs to be limited to the A values by using a set expression;

sum({<cat={'A'}>} TOTAL <COMPANY> amt)

The by dividing amt with the sum of As, you will now get the ratio per row for the corresponding dimensions.

amt / sum({<cat={'A'}>} TOTAL <COMPANY> amt)

Re: set analysis help for %

Thu, 17 Nov 2011 10:11:13 GMT

Hi toni,

thank you so much....