topic Re: How to make my both 2 expression display GP = 50.2% ? in QlikView

How to make my both 2 expression display GP = 50.2% ?

paulyeo11 — Mon, 27 Jun 2016 13:14:39 GMT

Hi All

Below expression return correct GP = 50.2% :-

(

sum({$<year = {$(=max(year)-1)}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} cost)

)

(

sum({$<year = {$(=max(year)-1)}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} sales)

)

This is because below 2 load script are working fine :-

if(@241:248T = '2',@137:151T*-1,@137:151T) as [sales],

if(@241:248T = '2',@122:135T*-1,@122:135T) as [cost],

Below return wrong GP = 49% :-

(

sum({$<year = {$(=max(year)-1)}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} profit_amt)

)

(

sum({$<year = {$(=max(year)-1)}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} sales)

)

This is because below script is not working fine :-

if(@241:248T = '2',@137:151T*-1,@137:151T)- (@122:135T ) as profit_amt,

Hope some one can help me modify the above script, so that i can get the same GP% for above 2 expression.

Paul

Re: How to make my both 2 expression display GP = 50.2% ?

paulyeo11 — Mon, 27 Jun 2016 13:15:29 GMT

my qvw

Re: How to make my both 2 expression display GP = 50.2% ?

MK_QSL — Mon, 27 Jun 2016 13:38:08 GMT

Check your profit calculation...

It's wrong for 2015, January... 2015, March (for example)

I mean sales-cost must equal to profit_amt which is not fulfilling.

Re: How to make my both 2 expression display GP = 50.2% ?

paulyeo11 — Mon, 27 Jun 2016 13:44:02 GMT

Hi Manish

I aware profit_amt is wrong , in load script I have sales and cost field, how to make use of these 2 field to compute the right profit_amt in load script ?

As now my load script for profit_amt field is wrong .

Paul

Sent from my iPhone

Re: How to make my both 2 expression display GP = 50.2% ?

MK_QSL — Mon, 27 Jun 2016 13:46:37 GMT

You can use

(SUM(Sales)-SUM(Cost)/SUM(Cost)

For Profit

1 - (SUM(Sales)/SUM(Cost))

Re: How to make my both 2 expression display GP = 50.2% ?

paulyeo11 — Mon, 27 Jun 2016 13:51:14 GMT

Hi Manish

I am aware how to write in expression for get the correct GP%.

What I don't know is load script for profit_amt.

I refer to how to write load script for profit_amt

Can you share with me how to modify it ?

Paul

Sent from my iPhone

Re: How to make my both 2 expression display GP = 50.2% ?

paulyeo11 — Mon, 27 Jun 2016 13:53:02 GMT

Hi Manish

If my profit_amt field load script is correct , then my both expression will return same GP%.

Sent from my iPhone

Re: How to make my both 2 expression display GP = 50.2% ?

MK_QSL — Mon, 27 Jun 2016 13:53:30 GMT

I checked your whole script and unable to find cost and sales... now how can I advice your what to write to get profit_amount in script?

Re: How to make my both 2 expression display GP = 50.2% ?

paulyeo11 — Mon, 27 Jun 2016 13:58:26 GMT

Hi Manish

241:248T = '2',@137:151T*-1,@137:151T) as ,

if(@241:248T = '2',@122:135T*-1,@122:135T) as ,

Can you pls help me combine above 2 into profit_amt ?

Sent from my iPhone

Re: How to make my both 2 expression display GP = 50.2% ?

MK_QSL — Mon, 27 Jun 2016 14:00:00 GMT

Honestly speaking,,, I don't understand what this expression is about...

Re: How to make my both 2 expression display GP = 50.2% ?

paulyeo11 — Mon, 27 Jun 2016 14:06:07 GMT

Hi Manish

cost field 122:135T and sales field 137:151T :-

241 is the sign either +ve or -ve represent as 1 or 2

if(@241:248T = '2',@122:135T*-1,@122:135T) as [cost],

if(@241:248T = '2',@137:151T*-1,@137:151T) as [sales],

Re: How to make my both 2 expression display GP = 50.2% ?

MK_QSL — Mon, 27 Jun 2016 14:19:03 GMT

if(@241:248T = '2',@137:151T*-1,@137:151T) - if(@241:248T = '2',@122:135T*-1,@122:135T) as Profit

Re: How to make my both 2 expression display GP = 50.2% ?

paulyeo11 — Mon, 27 Jun 2016 14:25:44 GMT

Hi Manish

Now my GP% for both expression are very closed. almost correct now.

Thank you

Paul