Expression help

[danielnevitt]

Hi,

I have the below expression that works fine:

sum(if(delivery_term_cd='DES', (title_transfer_dt-comp_d_dt),if(delivery_term_cd='FOB',(title_transfer_dt-bl_dt) ,0)))

However when I try to add multiple selections (like the below), I can't get the expression to work:

sum(if(delivery_term_cd='DES','DAT', (title_transfer_dt-comp_d_dt),if(delivery_term_cd='FOB',(title_transfer_dt-bl_dt) ,0)))

Any help will be much appreciated.

Thanks,

Daniel

[sunny_talwar]

Try using Match function in this case:

=Sum(If(Match(delivery_term_cd, 'DES' , 'DAT'), (title_transfer_dt-comp_d_dt), If(delivery_term_cd='FOB', (title_transfer_dt-bl_dt), 0)))

[danielnevitt]

Thanks Sunny, that works great.

Would it be possible to restrict this field to only show where the expression does not equal zero?

Regards,

Daniel

[sunny_talwar]

Try if removing the 0 at the end helps here;

=Sum(If(Match(delivery_term_cd, 'DES' , 'DAT'), (title_transfer_dt-comp_d_dt), If(delivery_term_cd='FOB', (title_transfer_dt-bl_dt))))

[sunny_talwar]

Or this:

If(Sum(If(Match(delivery_term_cd, 'DES' , 'DAT'), (title_transfer_dt-comp_d_dt), If(delivery_term_cd='FOB', (title_transfer_dt-bl_dt), 0))) > 0, Sum(If(Match(delivery_term_cd, 'DES' , 'DAT'), (title_transfer_dt-comp_d_dt), If(delivery_term_cd='FOB', (title_transfer_dt-bl_dt), 0))))

[danielnevitt]

Thanks Sunny.

The below code works:

sum(if(Match(delivery_term_cd,'DAF','DAP','DAT','DDP','DDU','DEL','DES'), (title_transfer_dt-comp_d_dt),if(delivery_term_cd='FOB',(title_transfer_dt-bl_dt) ,0)))

How when I tried to add another selection to the second part of the expression, these results are not shown:

sum(if(Match(delivery_term_cd,'DAF','DAP','DAT','DDP','DDU','DEL','DES'), (title_transfer_dt-comp_d_dt),if(Match(delivery_term_cd,'FOB','FCA'), (title_transfer_dt-comp_d_dt),0)))

Regards,

Daniel

[sunny_talwar]

You might have put the incorrect field name in your true section of second if statement Do you need title_transfer_dt-comp_d_dt or title_transfer_dt-bl_dt here ->

sum(if(Match(delivery_term_cd,'DAF','DAP','DAT','DDP','DDU','DEL','DES'), (title_transfer_dt-comp_d_dt),if(Match(delivery_term_cd,'FOB','FCA'), (title_transfer_dt-comp_d_dt),0)))

[danielnevitt]

Hi Sunny,

I did require title_transfer_dt-bl_dt in the second section.  I'm basically trying to code if 'DAF', 'DAP', 'DAT' etc then title_transfer_dt - comp_d_dt and if 'FOB','FCA' etc then title_tranfer_dt-bl_dt.

Does that make sense?

Regards,

Daniel

[sunny_talwar]

Do you have delivery_term_cd as a dimension? If you do, can you try this:

RangeSum(Sum({<delivery_term_cd = {'DAF','DAP','DAT','DDP','DDU','DEL','DES'}>} title_transfer_dt-comp_d_dt),                   Sum({<delivery_term_cd = {'FOB','FCA'}>} title_transfer_dt-bl_dt))