Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
tracycrown
Creator III
Creator III

Variables Computation

Dear all

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

   

StoreAccountTypeDetails201320142015
L1RevenueRetailProduct 1          200,000        243,000               295,000
L1RevenueRetailProduct 2          185,450        177,490               170,045
L1Cost of SalesRetailProduct 1          160,000        206,550               253,700
L1Cost of SalesRetailProduct 2          142,797        143,767               136,036

Formula :

1. Gross Profit = Revenue - Cost of Sales

2. Gross Margin = Gross Profit / Revenue

Thank you

Tracy

17 Replies
tracycrown
Creator III
Creator III
Author

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
Creator III
Creator III

Hi Tracy

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

tracycrown
Creator III
Creator III
Author

Dear Girirajsinh Vaghela

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

Thank you

Tracy

girirajsinh
Creator III
Creator III

Hi Tracy

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

Please find attached document.

tracycrown
Creator III
Creator III
Author

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
Creator III
Creator III

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
Creator III
Creator III
Author

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
Creator III
Creator III

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.