Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Sorry - this is another simple problem I'm stuck with.
Removing all descriptive fields to express the problem as simply as possible we have two sources of data
Stock-Receipts:
Batch# Receipt-Date
A1 15/08/15
A2 16/08/15
A2 16/08/15
A3 17/08/15
Shipments-to-Customers
Batch# Ship-Date
A2 21/08/15
A3 24/08/15
The guage would display the avrage days taken to ship a product (considering only batches actually shipped) with a dimension like
=num(AVG([Ship-Date] - [Receipt-Date]),'###.#')
My problem is, as per the example above, there may be multiple receipts on the same day (and maybe multiple shipments to customers)
Batch A2 has taken 5 days to ship and batch A3 has taken 7 days to ship so I want an average of 6 days on the guage
but because there are two receipts of A2 the average from num(AVG([Ship-Date] - [Receipt-Date]) is coming as (5 + 5 + 7)/3 = 5.7 instead of (5 + 7)/2 = 6
Somewher i need a DISTINCT but I can't seem to get the right answer
Tks for you help
Dave
HI
You should have one Fact table by joining the two table ...
LOAD
Batch,
Receipt-Date
FROM
[...\ReceiptTable.xls]
(biff, embedded labels, table is Sheet1$);
Right Join
LOAD
Batch,
Ship-Date
FROM
[...\ShippTable.xls]
(biff, embedded labels, table is Sheet1$);
Having that you should have one Table
Batch Ship-Date Receipt-Date
A2 21/08/15 16/08/15
A3 24/08/15 17/08/15
Then you can calculate the average shipping date
For your sample this should work:
=Avg(Aggr(Avg([Ship-Date] - [Receipt-Date]), Batch#))
If you cannot identify which receipt for A2 ties to which shipment for A2, then I would use:
=Avg([Ship-Date]) - Avg([Receipt-Date])
or
=Min([Ship-Date]) - Max([Receipt-Date])
That assumes that Batch# is a dimension. To get the average across all batches:
=Avg(Aggr(Avg([Ship-Date]) - Avg([Receipt-Date]), [Batch#])
Tsk all - I think all seem to work, but being lazy I took the last reply - tks everyone for your help