Case when in QV

[Anonymous]

Hi

I am trying to create a formula in a pivot table where I use case when and <> and LIKE

Something like:

Count distinct(case when [UOM for FROM_QTY.] <> 'RC' and TO_LOCATION LIKE '2%' then to_lic, else 0 end)

Have been looking at different treads and I came up with this:

=count({$<[UOM for FROM_QTY.]={"*"} - {'RC'}>}{$<TO_LOCATION={'2*'}>} distinct TO_LIC)

Also I need the counterpart of this one, so I tried:

=count({$<[UOM for FROM_QTY.]={'RC'}>}{$<TO_LOCATION={'2*'}>} distinct TO_LIC)

But that does not seem to give what I need,

They end up having the same count.

Any suggestions on why or how this is best solved?

BR

Dan

[Anonymous · Accepted solution]

Tried these two as well

=COUNT(DISTINCT if(not([UOM for FROM_QTY.] like 'RC') and TO_LOCATION LIKE '2*',TO_LIC,0))

=COUNT(DISTINCT if(([UOM for FROM_QTY.] like 'RC') and TO_LOCATION LIKE '2*',TO_LIC,0))

I now get different figures between these two, but not the fiugres I expected.

BR

Dan

[nirav_bhimani]

HI,

In the if condition we never use else part. Remove that part.

Try to use - sign before =  so that it will work like exclude.

=count({$<[UOM for FROM_QTY.] -={'RC'}>}{$<TO_LOCATION={'2*'}>} distinct TO_LIC)

Hope this will help.

try same syntax with P() - E () using set analysis.

Regards, 

Nirav Bhimani