QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Valued Contributor II

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

1 Solution

Accepted Solutions
MVP

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
12 Replies
Valued Contributor II

my qvw

MVP

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.

Valued Contributor II

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

MVP

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))

Valued Contributor II

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

Valued Contributor II

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

MVP

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?

Valued Contributor II

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

MVP

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

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