Hello, we are trying to build a dashboard for our buyers and one of the widgets is for excess inventory. Thanks to the PUG we were able to use a formula to remove negative values (^case when{a}-{b}< 0 then ‘0’ else {a}-{b} end).
When we look at the excess by item/sku level it looks great. When we group by the dimension by buyer, the total excess is showing zero for most of them.
The excess is not zero because they do have items with an excess value.
I
I know the problem is in the formula. We need to eliminate all negative $ amount at the sku level prior to summarizing by buyer.
Do I make sense?
Have you tried putting the CASE WHEN in the advanced search area instead of as a column in custom?
Not sure on the hierarchy of how and when calculations are applied but I had a report where I joined effectively two streams and when I used the Custom view it waited till the end, but in Advanced Search I could filter each stream independantly.
Its a calculated field, can you do that in the advanced search?
Hmm, seems an odd one - I’ve replicated your report and can see that the column doens’t total as expected - maybe there’s a thing where columns with CASE WHEN in don’t total - I think the 0 is being treated as text and not a number and therefore breaks the sum.
However, if you could live with only seeing those items where there is an excess then you could do the following:
- Make the formula in Custom just A - B
- Use advanced filter to filter only those records where the result of that sum is actually a positive number.
You should then be able to use the Nested mode to cut and dice that as you need
Sorry for the delay! We’ve decided that we will get a Phocas consultant to help us with this and several other inventory and GL reports. Thanks for you help, i will keep this in mind!
