topic Re: Expression help in QlikView

Expression help

danielnevitt — Mon, 25 Jan 2016 11:26:17 GMT

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

Re: Expression help

sunny_talwar — Mon, 25 Jan 2016 11:28:14 GMT

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)))

Re: Expression help

danielnevitt — Mon, 25 Jan 2016 11:33:22 GMT

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

Re: Expression help

sunny_talwar — Mon, 25 Jan 2016 11:34:55 GMT

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))))

Re: Expression help

sunny_talwar — Mon, 25 Jan 2016 11:35:58 GMT

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))))

Re: Expression help

danielnevitt — Mon, 25 Jan 2016 11:49:36 GMT

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

Re: Expression help

sunny_talwar — Mon, 25 Jan 2016 11:54:21 GMT

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)))

Re: Expression help

danielnevitt — Mon, 25 Jan 2016 12:08:06 GMT

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

Re: Expression help

sunny_talwar — Mon, 25 Jan 2016 12:18:31 GMT

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))