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

[girirajsinh]

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)

[tracycrown]

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]

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]

PFA

I have tried to create them here in attached

[vinieme12]

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

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

[tracycrown]

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]

Check the attached App

[girirajsinh]

Do you check if the attached working fine for you ?