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

1 Solution

Accepted Solutions
girirajsinh
Creator III
Creator III

Hi Tracy

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

Please find attached document.

View solution in original post

17 Replies
girirajsinh
Creator III
Creator III

Why don't you consider to put it in script ?

Like

Load

*,

(Revenue - Cost of Sales ) as [Gross Profit]

(Gross Profit / Revenue) as [Gross Margin]

Resident CrossTableName

Anil_Babu_Samineni

Does this you need from Front End or Back End.

First you need cross table for Year as Data

And then You can use these formulas

Dim as you need

And Expressions, Something like this

1. Gross Profit = Sum({<Account ={'Revenue'}>}Data) - Sum({<Account = {'Cost of Sales'} >} Data)

2. Gross Margin = Column(1) / Sum({<Account ={'Revenue'}>}Data)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
tracycrown
Creator III
Creator III
Author

Dear Girirajsinh Vaghela

Thanks for your advice. I have tried but it does not work, please refer to attached QV fille.

Thank You

Tracy

tracycrown
Creator III
Creator III
Author

Dear Anil Babu

Thanks for your advice.

Please refer to attached QV file and advise how to arrange Gross Profit and Gross Margin below the Cost of Sales ?.

Thank you

Tracy

girirajsinh
Creator III
Creator III

PFA

I have tried to create them here in attached

vinieme12
Champion III
Champion III

I think a data model like this will be easier to work with

If you feel the same , i'll post the script!

2Capture.JPG

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
tracycrown
Creator III
Creator III
Author

Dear Vineeth Pujari

Thanks for your quick response. The answer is correct but which method did you use Script or Expression ?.

Thank You

Tracy

vinieme12
Champion III
Champion III

Check the attached App

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
girirajsinh
Creator III
Creator III

Do you check if the attached working fine for you ?