Variables Computation

[tracycrown]

Dear all

Kindly help to advise how to compute variables called Gross Profit and Gross Margin based on following raw data :

Store	Account	Type	Details	2013	2014	2015
L1	Revenue	Retail	Product 1	200,000	243,000	295,000
L1	Revenue	Retail	Product 2	185,450	177,490	170,045
L1	Cost of Sales	Retail	Product 1	160,000	206,550	253,700
L1	Cost of Sales	Retail	Product 2	142,797	143,767	136,036

Formula :

1. Gross Profit = Revenue - Cost of Sales

2. Gross Margin = Gross Profit / Revenue

Thank you

Tracy

[tracycrown]

Dear Vineeth Pujari

Thanks for your App.

I found it too difficult to understand, is there a way to write a simple script using the concept of SET vA etc. same as the ones (1st few lines) in my QV file.

Thank you

Tracy 

[girirajsinh]

Hi Tracy

Please find the attached qvw. check if that is fine for you.

[tracycrown]

Dear Girirajsinh Vaghela

Thanks for your kind advice. The answer is correct but how to arrange in following format ?

Thank you

Tracy

[girirajsinh · Accepted solution]

Hi Tracy

You may use Pivot or Stright Table, something like this way.

Please find attached document.

[tracycrown]

Dear Girirajsinh Vaghela

Thank you so much for your kind effort.

The answer is correct but the format is different from what my client's requirement.

Is it possible to arrange revenue, cost of sales, gross profit and gross margin under "Account" which is the same format I posted on Jan 22, 2017 ?.

Thank you

Tracy

[girirajsinh]

use

num(sum(Revenue),'###,##0')  instead of sum(Revenue)

and for gross margin something like this

num(sum(GM),'#0.0%')

Same you can achieve by formating from Number tab.

Please read more about Num() here.

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/FormattingFunctions/Nu...

[tracycrown]

Dear Girirajsinh Vaghela

You have mistaken what I want, I do not mean the numeric and percentage date formatting.

My client insisted that the following report format : Store, Account, Type, Details , 2013, 2014, 2015.

Thanks

Tracy

[girirajsinh]

Hi Tracy

Please find the attached one. It brings the new format and logic for loading.

the earlier what I suggested was more robust way.

this one is developed till Gross Profit. You can then proceed to derive Gross Margin in similar way, but thats the direction for now which you cna follow.