Expressions and nested IF's with filters

[-Steve-]

Hi,

I am trying to decomplicate this problem - 

My current expression is working, however, the Calculated and Actual Freight values can be both zero, which messes up the averages.

if(isnull([Actual Freight Cost_Curr]),sum({<va_id -= {'11','12','13'}>}[Calculated Freight Cost_Curr]),sum({<va_id -= {'11','12','13'}>}[Actual Freight Cost_Curr]))
/
count({<va_id -= {'11','12','13'}>}Distinct Tour_Nr)

Is there a way to simplify the expression, to only pick up where va_id =11,12,13 and use Actual Cost if it is > 1 , or calculated cost if also > 1

hope that makes sense ?

[aruneshgupta]

You may try to filter in script which might help!

 Best Regards,

Arunesh 

[Aditya_Chitale]

@-Steve- 

Didn't quiet get your point.

You have described to take Actual & Calculated cost  for va_Id 11, 12 & 13 where sum of  both fields >1.

If sum is not zero than  why would you want to take va_id = 11,12 & 13 ? the Average will be calculated in main expression itself having condition va_id -= 11,12 & 13.

As for the part where Average is getting messed due to Actual & Calculated Cost being zero, You can add an extra nested if() condition to check if values are zero.

if(isnull([Actual Freight Cost_Curr]),
if(sum({<va_id -= {'11','12','13'}>}[Calculated Freight Cost_Curr])=0, sum({<va_id = {'11','12','13'}>}[Calculated Freight Cost_Curr]), sum({<va_id -= {'11','12','13'}>}[Calculated Freight Cost_Curr])),
if(sum({<va_id -= {'11','12','13'}>}[Actual Freight Cost_Curr])=0, sum({<va_id = {'11','12','13'}>}[Actual Freight Cost_Curr]), sum({<va_id -= {'11','12','13'}>}[Actual Freight Cost_Curr]))
)

/

count({<va_id -= {'11','12','13'}>}Distinct Tour_Nr)

Hope this helps !!

Regards,

Aditya

[-Steve-]

Hi, 

Thanks for your replies - 

Okay, so the problem is, when pulling the data from the source I am getting the odd "null" field, where the actual or calculated values are not populated in the application, so what I need to do, is find the average Freight Cost (actual if exists, or failback to the calculated if actual is zero or null), then divide by the number of Tours.

So, possibly in the load - I could check if values exists and have a "useThisValue", bringing that through into the expression.

[vinieme12 · Accepted solution]

create a new field during data load

COALESCE(Actual Freight Cost_Curr,Calculated Freight Cost_Curr) as freight_Cost

or

ALT(Actual Freight Cost_Curr,Calculated Freight Cost_Curr) as freight_Cost

then just use

sum({<va_id -= {'11','12','13'}>}[freight_Cost])
/
count({<va_id -= {'11','12','13'}>}Distinct Tour_Nr)

[-Steve- · Accepted solution]

Great - thank you.

This would remove the null issue, however, if the actual is zero, but calculated is 400 - then I would want to bring through the calc (400) value - not the zero (actual) value.

COALESCE(Actual Freight Cost_Curr,Calculated Freight Cost_Curr,600) as freight_Cost

am I right in thinking this would work.

[-Steve- · Accepted solution]

sum( {<va_id -= {'11','12','13'}>} coalesce( [Actual Freight Cost_Curr], [Calculated Freight Cost_Curr] , 600 ) )
/
count({<va_id -= {'11','12','13'}>}Distinct Tour_Nr)