12 Replies Latest reply: Jun 27, 2016 10:25 AM by Yeo Poh sai

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

Hi All

Below expression return correct GP = 50.2% :-

1-

(

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

my qvw

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

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% ?

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% ?

You can use

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

For Profit

or

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

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

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% ?

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% ?

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% ?

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

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

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% ?
 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% ?

Hi Manish

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

Thank you

Paul

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

Hi Manish

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

Sent from my iPhone