Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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