topic Re: Calculating GM% in QlikView

Calculating GM%

Fri, 10 Jul 2015 10:41:58 GMT

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

Re: Calculating GM%

swuehl — Fri, 10 Jul 2015 11:09:12 GMT

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.

Re: Calculating GM%

robert_mika — Fri, 10 Jul 2015 11:13:02 GMT

Format as %

Re: Calculating GM%

Fri, 10 Jul 2015 14:26:39 GMT

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?

Re: Calculating GM%

Fri, 10 Jul 2015 14:30:28 GMT

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.

Re: Calculating GM%

swuehl — Sat, 11 Jul 2015 10:02:05 GMT

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)

)

Re: Calculating GM%

Tue, 14 Jul 2015 11:24:27 GMT

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),#,###)

)