This field is required.
Only these extensions are allowed(.jpg, .JPG, .jpeg, .JPEG, .gif, .GIF, .png, .PNG)
Tags cannot contain the characters ' /, \\, #, ?, or ; >,< '
Only these extensions are allowed(.zip,.ZIP,.pdf,.PDF,.qvf,.QVF,.qvw,.QVW)
Discussion Board for collaboration related to QlikView App Development.
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
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
You should have one Fact table by joining the two table ...
(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])
=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