Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HNY folks...
Got a painful task that's proving to be a bit of a pig to solve...
Basically:
I have a straight table that has 3 dimensions and an expression..
OrderID | Warehouse | Customer | Total Order Units |
---|---|---|---|
1 | Edinburgh | Smith | 77.00 |
1 | Edinburgh | Jones | 77.00 |
1 | Edinburgh | Brown | 77.00 |
Ignore the logic of the data, the basic premise is that one order can sit in one warehouse, but have multiple customer names.
The expression total (77.00) is the total order value - what I need to do, is to assume it's a straight split, so basically have the 77 split equally between all the Customers in the order (25.67)...given other orders will exist that have more (or less) customers, I need an expression that will count the customers per order per warehouse and divide the total order value....
Confused ? Welcome to the club.
Thanks in advance....
(Don't ask for a QVW as there isn't one )
Thanks Clever
Nearly - but with a bit of tweaking I got there..
Count(TOTAL <Warehouse>Customer)
Your answer was limited to only that selection (ie OrderID 1 & Warehouse Edinburgh) - when you expanded back out to the full order id history, the count expanded to.
Thanks though - can't believe I missed that
count(total <OrderId> Customer) will bring you how much customers are in your Order, so you can divide your Total Unit Units by this value
Hi Graeme,
Use AVG() function for above requirement.
=AGGR(AVG(Order_Units),Customer,Warehouse)
Regards,
Kiran
Thanks Clever
Nearly - but with a bit of tweaking I got there..
Count(TOTAL <Warehouse>Customer)
Your answer was limited to only that selection (ie OrderID 1 & Warehouse Edinburgh) - when you expanded back out to the full order id history, the count expanded to.
Thanks though - can't believe I missed that