set analysis help for %

[#1]

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.

[ToniKautto]

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

[#3]

hi

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)

[ToniKautto]

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.

[#5]

hi

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.

[ToniKautto · Accepted solution]

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)

[#7]

Hi toni,

thank you so much....