If condition with multiple values in expression

[neena123]

Hello!!

I need some help on creating an expression that selects all orders with any one of these combinations:

Last Status:520  Next Status 540

Last Status:523  Next Status 540

Last Status:525  Next Status 540

Here is what I got

IF([Last Status]=523 AND [Next Status]=540,COUNT(DISTINCT([Unique Order Key])),IF([Last Status]=520 AND [Next Status]=540,COUNT(DISTINCT([Unique Order Key])),IF([Last Status]=525 AND [Next Status]=540,COUNT(DISTINCT([Unique Order Key])))))

I tried multiple ways and nothing seems to work. Any help is appreciated!!!

[vishsaggi]

may be try this?

= Count(DISTINCT IF(Match(LastStatus, 520,523,525)>0 AND NextStatus = 540, UniqueOrderKey))

[neena123]

Hi Vishwarath,

Thanks for the reply! I actually tried that but sadly it doesn't work. I think it reads it and sees the 520 status and matches with that first so it only brings in data with a last status of 520 and Next status 540

[vishsaggi]

can you share some sample data and expected output that would help?

[maxgro]

try this

count({$ <[Next Status]={540}, [Last Status]={520,523,525}>} DISTINCT [Unique Order Key])

[neena123]

Sure! I just had to scramble some of the data.