Calculating GM%

[#1]

Dear fellow experts,

I have a table like this which is loaded in the script

Field                         Location          YTD     Month     Budget

Turnover                    Japan               100     20               110

Gross margin             Japan                 40     10               50

Operating profit          Japan                 10     2                 20           

Turnover                    India                1000     500               1100

Gross margin             India                 600         200               750

Operating profit          India                    300     100               350

How can i calculate the GM% as taking Gross margin divided by turnover in the resulting straight table or pivot table that i cra

[swuehl]

Create a straight table with dimension Location, then as Expression for YTD GM%

=Sum({<Field = {'Gross margin'}>} YTD) / Sum({<Field = {'Turnover'}>} YTD)

Month and Budget GM% accordingly.

[robert_mika]

Format as %

[#4]

Hi both,

However, what if i want the GM% to appear as an additional field. That means when i select my straight table in Qlikview , rather than choosing location as the dimension, i will like to choose Fields as the dimension and include an additonal line called GM%.

Possible to be done?

[#5]

So the output should be something like this..

Field                                   YTD    

Turnover                              1100                 

Gross margin                         640                  

GM %                                    58.2%

Operating profit                      310     

Month and Budget GM% accordingly.

[swuehl · Accepted solution]

Table:

LOAD * INLINE [

Field,                         Location,          YTD,     Month ,    Budget

Turnover,                    Japan,               100,     20 ,              110

Gross margin,             Japan   ,              40  ,   10    ,           50

Operating profit,          Japan  ,               10  ,   2     ,            20          

Turnover,                    India ,               1000 ,    500 ,              1100

Gross margin,             India    ,             600    ,     200 ,              750

Operating profit,          India   ,                 300 ,    100  ,             350

];

Dim:

LOAD Field Resident Table;

LOAD 'GM %' as Field autogenerate 1;

Then create a straight table with dimension Field and expression

= if(Field = 'GM %' ,

     num( Sum(TOTAL {<Field = {'Gross margin'}>} YTD) / Sum(TOTAL {<Field = {'Turnover'}>} YTD),'0.0%','.',','),

      sum( YTD)

)

[#7]

Hi Swuehl,

Thanks, the above suggestion worked for me! Anyway, rather than loading a autogenerate field in the table, i just entered GM% in the spreadsheet itself and then load into the Qlikview.

After that i inputted the straight table and typed in the formula as suggested. However i did a small change at the end so that the Sum(YTD) return an integer

= if(Field = 'GM %' ,

     num( Sum(TOTAL {<Field = {'Gross margin'}>} YTD) / Sum(TOTAL {<Field = {'Turnover'}>} YTD),'0.0%','.',','),

      num(sum( YTD),#,###)

)