- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Expressions and nested IF's with filters
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 ?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
If a post helps to resolve your issue, please accept it as a Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You may try to filter in script which might help!
Best Regards,
Arunesh
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
If a post helps to resolve your issue, please accept it as a Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)