Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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)
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.
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)
You may try to filter in script which might help!
Best Regards,
Arunesh
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
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.
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)
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.
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)