[Cloud]

Hello All,

I've created a search here to show me all items sold to date by items. Amount and QTY Sold but then added Gross Margin and Profit to show me the most profitable items. It's working for some but not for all. I've attached screenshots (1st Level and 2 screenshots showing 2nd Level drill-down) it's not working for either.

For example – 0219P

Top Level shows 24 items Sold but only had 23 lines when I drill down. The gross profit is $14.04 for Each item, therefore it should be 336.96 (because 24 items sold) However, it's showing $322.92.

When I drill down to this item, the profit is correct for all except that one transaction which has 2 items sold, it still shows 14.04, when it should be 14.04 x 2 = $28.08.

Second Example – SS3888MD

Top Level shows 10 items sold but only 4 lines when I drill down. The gross profit is $11.01 for Each item, therefore it should be $110.10 (because 10 items sold). However, it's showing me 44.04.

When I drill down to this item, the profit is correct except for the 2 transactions which have 4 items sold. The margins for those should show $44.04.

So without confusing myself anymore, I'm going to leave it here and I'm sure you lovely people understand what issues I'm having. I've been wasting almost an hour behind this and cannot get this to work correctly.

Attached is also the criteria and also the Results Page so you have a better understanding of how I've run this search.

Thanks
This is a cached copy. Click here to see the original post.

[Veda]

For 0219P, looks like there were only 23 lines with one line having quantity as 2. Hence total quantity is 24. If you multiply Quantity with Gross profit, you should get total profit.

thanks,

Veda.

---

Cloud replied on 10/05/2017, 01:48 AM: Hi Veda, I know Sorry if there was any confusion but I thought I clearing explained why it was showing, I just need to fix the formula and find out why it’s not working like its supposed too. I can’t forward this to my boss, he won’t be happy as it’s incorrect.

Also, I have tried to use – ({item.onlineprice}-{item.vendorcost})*CASE WHEN TO_CHAR({trandate},’YYYY’)=’2017′ THEN {quantity} ELSE NULL END…. but that is also incorrect on some items.

Thanks

[Veda]

there is another possibility. There could be have two lines of same item on an order and hence the sum has given the quantity as 2. In both cases, multiplying quantity with profit would give gross profit.

thanks,

Veda.

---

Cloud replied on 10/09/2017, 02:45 PM: Veda, I know this already (see my OP, I mentioned this). I just need help getting the criteria correct which will give me the correct results.

[Cloud]

Bump. Anyone?

[Cloud]

Anyone, please?

[Author]

Posts