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]

How about Sum([GROSS PROFIT])/Sum([NET REVENUE])

[Anonymous]

Sorry mate, I have made this way but I was asked to do the way below:

For the first case your answer helped me.

The new desirable result is:

                                                       JANUARY

GROSS PROFIT                            150

NET REVENUE                               50

GROSS MARGIN                        (150/50) = 300%

[sunny_talwar]

Do you have a sample where you are trying this out? May be easy to see how the table and data looks like

[vishsaggi]

In the Pivot table Use Month as Dimension and use these below 3 in the Expression tab. It works fine at my end.

Dimension - Month

Expression tab:

1. Sum([Gross Profit])     

2. Sum([Net Revenue])

3. (Sum([Gross Profit])/Sum([Net Revenue])) * 100 & '%'     Label As [Gross Margin]

Try this and let us know.

Thanks

V.

[vishsaggi]

Are you looking for something like below:

[Anonymous]

Sunny, here is exactly what I am doing in script with another data:

TABLE_1:

LOAD * INLINE [

    ID, Product, GROSS PROFIT, NET REVENUE, Month

    1, Pineapple, 100, 10, January

    2, Apple, 50, 40, January

    3, Melon, 30, 30, January

];

Calculation:

load *,

     [GROSS PROFIT] - [NET REVENUE] as [GROSS MARGIN]

Resident TABLE_1;

drop table TABLE_1;

NoConcatenate

TMP:

load

   ID,

   Product,

   Month, 

   [GROSS PROFIT],

   [NET REVENUE],

   [GROSS MARGIN]

Resident Calculation;

drop table Calculation;

FINAL:

CrossTable(TYPE, REVENUE, 3)

load *

Resident TMP;

drop Table TMP;

Another question. Do you know how to sort lines, having GROSS MARGIN, GROSS PROFIT and then NET REVENUE?

Thanks.

[sunny_talwar · Accepted solution]

This?

[Anonymous]

Thank you very much, Sunny!

It helped a lot.

Best Regards.

[Anonymous]

Hi Sunny,

What if I have the same pivot table with one more dimension:

Dimension

Product

Type

Expression:

Using the same expression in this case gives the same value for every product, even removing the 'TOTAL' out.