Calculating percentages in pivot table

[Anonymous]

Hi all,

I am doing sales results in qlikview and would like to calculate the total percentage in a line, using pivot table. For each product, I calculate the 'net revenue' , 'gross profit' and divinding gross profit per NET REVENUE we have the GROSS MARGIN. This is only for one product, but there is a problem for obtaining the GROSS MARGIN  for more than one in a pivot table, as I am using SUM() to have total per month.

What I would like to do is even the user selecting one or more product, the GROSS MARGIN would be the result of division (GROSS PROFIT/(NET REVENUE)).

Please see table below with desirable result:

TABLE A:

ID    PRODUCT      GROSS PROFIT          NET REVENUE         GROSS MARGIN               MONTH

1        pineapple         100                                     10                        (100/10) = 1000%             JANUARY

2       apple                     50                                     40                       (50/40)   =       80%            JANUARY

OUTPUT TABLE:           

MONTH                GROSS PROFIT          NET REVENUE         GROSS MARGIN        

JANUARY                  150                                      50                          (150/50) = 300%

IF I use the sum() it will return the value of 1080% and not the correct (300%).

does anyone knows how to do that?

[sunny_talwar]

This?

Expression used:

=Pick(Match(TYPE, 'GROSS PROFIT', 'NET REVENUE', 'GROSS MARGIN'),

Sum(REVENUE),

Sum(REVENUE),

Num(Sum(TOTAL <Product> {<TYPE = {'GROSS PROFIT'}>}REVENUE)/Sum(TOTAL <Product> {<TYPE = {'NET REVENUE'}>}REVENUE), '##.0%'))

[Anonymous]

Yes!

It worked perfectly!

Thank you very much.