topic Re: Variables Computation in QlikView

Variables Computation

tracycrown — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Variables Computation

girirajsinh — Sat, 21 Jan 2017 12:30:20 GMT

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

Load

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

(Gross Profit / Revenue) as [Gross Margin]

Resident CrossTableName

Re: Variables Computation

Anil_Babu_Samineni — Sat, 21 Jan 2017 13:28:11 GMT

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)

Re: Variables Computation

tracycrown — Sat, 21 Jan 2017 14:22:10 GMT

Dear Girirajsinh Vaghela

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

Thank You

Tracy

Re: Variables Computation

tracycrown — Sat, 21 Jan 2017 14:28:08 GMT

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

Re: Variables Computation

girirajsinh — Sat, 21 Jan 2017 14:56:54 GMT

PFA

I have tried to create them here in attached

Re: Variables Computation

vinieme12 — Sat, 21 Jan 2017 15:19:56 GMT

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

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

Re: Variables Computation

tracycrown — Sat, 21 Jan 2017 16:23:05 GMT

Dear Vineeth Pujari

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

Thank You

Tracy

Re: Variables Computation

vinieme12 — Sat, 21 Jan 2017 16:31:25 GMT

Check the attached App

Re: Variables Computation

girirajsinh — Sat, 21 Jan 2017 22:02:09 GMT

Do you check if the attached working fine for you ?

Re: Variables Computation

tracycrown — Sun, 22 Jan 2017 00:55:19 GMT

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

Re: Variables Computation

girirajsinh — Sun, 22 Jan 2017 07:08:35 GMT

Hi Tracy

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

Re: Variables Computation

tracycrown — Mon, 23 Jan 2017 00:12:50 GMT

Dear Girirajsinh Vaghela

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

Thank you

Tracy

Re: Variables Computation

girirajsinh — Mon, 23 Jan 2017 08:15:46 GMT

Hi Tracy

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

Please find attached document.

Re: Variables Computation

tracycrown — Mon, 23 Jan 2017 23:36:18 GMT

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

Re: Variables Computation

girirajsinh — Tue, 24 Jan 2017 05:32:29 GMT

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/Num.htm

Re: Variables Computation

tracycrown — Tue, 24 Jan 2017 07:40:11 GMT

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

Re: Variables Computation

girirajsinh — Tue, 24 Jan 2017 10:04:16 GMT

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.