Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
is there a way to simplify this if statement??? with respect to performance
Are your if statements nested or do you use them in separate charts ?
i am using in chart expression
Yes, but you are using it in different expressions or nested in the same expression ?
nested in same expression.
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.
Hi,
I think You don't need IF()
Maybe
Margin --> Sum(profit)/Fabs(Sum(revenue))
Regards,
Antonio
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))