topic Re: how to simplify this if statement in QlikView

how to simplify this if statement

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

hi all

i have this condition, regardless of revenue if profit is positive number i have to show +ve margin otherwise negative number

how can I achieve this?

if sum(profit) < 0 and sum(revenue) < 0, -(sum(profit)/sum(revenue)

if sum(profit) < 0 and sum(revenue) > 0, -(sum(profit)/sum(revenue)

if sum(profit)> 0 and sum(revenue) < 0, (sum(profit)/sum(revenue)

if sum(profit)> 0 and sum(revenue) > 0, (sum(profit)/sum(revenue)

can you simplify the above if statement?

Regards

Gautham

Re: how to simplify this if statement

micheledenardi — Thu, 01 Jun 2017 09:34:40 GMT

you can create 3 variables:

1) vTotalProfit: sum(profit)

2) vTotalRevenue: sum(revenue)

3) vProfit/Revenue: (sum(profit)/sum(revenue)

and re-write your expression like:

if($(vTotalProfit) < 0 and $(vTotalRevenue)<0, -$(vProfit/Revenue))

if($(vTotalProfit) < 0 and $(vTotalRevenue) > 0, -$(vProfit/Revenue))

if($(vTotalProfit) > 0 and $(vTotalRevenue) < 0, $(vProfit/Revenue))

if($(vTotalProfit) > 0 and $(vTotalRevenue) > 0, $(vProfit/Revenue))

Re: how to simplify this if statement

gauthamchilled — Thu, 01 Jun 2017 09:35:59 GMT

is there a way to simplify this if statement??? with respect to performance

Re: how to simplify this if statement

micheledenardi — Thu, 01 Jun 2017 09:38:31 GMT

Are your if statements nested or do you use them in separate charts ?

Re: how to simplify this if statement

gauthamchilled — Thu, 01 Jun 2017 09:40:50 GMT

i am using in chart expression

Re: how to simplify this if statement

micheledenardi — Thu, 01 Jun 2017 09:42:53 GMT

Yes, but you are using it in different expressions or nested in the same expression ?

Re: how to simplify this if statement

gauthamchilled — Thu, 01 Jun 2017 09:44:22 GMT

nested in same expression.

Re: how to simplify this if statement

micheledenardi — Thu, 01 Jun 2017 09:52:17 GMT

could be this ?

if(

sum(profit) < 0 and sum(revenue) <> 0, -(sum(profit)/sum(revenue))

if(sum(profit)> 0 and sum(revenue) <> 0, (sum(profit)/sum(revenue))

)

but if you want to improve your document performance you have to use variable or do the sum() during load.

Re: how to simplify this if statement

antoniotiman — Thu, 01 Jun 2017 09:57:41 GMT

Hi,

I think You don't need IF()

Maybe

Margin --> Sum(profit)/Fabs(Sum(revenue))

Regards,

Antonio

Re: how to simplify this if statement

Sergey_Shuklin — Thu, 01 Jun 2017 09:58:20 GMT

Hello, Gautham!

This is little bit shorter:

if((sum(profit)+sum(revenue))<0 or (sum(profit)<0 and sum(revenue)>0),-(sum(profit)/sum(revenue))

if((sum(profit)+sum(revenue))>0 or (sum(profit)>0 and sum(revenue)<0),(sum(profit)/sum(revenue))

Re: how to simplify this if statement

Anil_Babu_Samineni — Thu, 01 Jun 2017 10:04:56 GMT

May be this?

if(RangeSum(profit, revenue) < 0, -RangeSum(profit,revenue),

if(sum(profit) < 0 and sum(revenue) > 0, -RangeSum(profit, revenue)

if(sum(profit)> 0 and sum(revenue) < 0, RangeSum(profit,revenue)

if( RangeSum(profit, revenue) > 0, RangeSum(profit,revenue)))))