Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Removing duplicate receipts to obtain average on a guage display

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

4 Replies
ziadm
Specialist
Specialist

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

sunny_talwar

For your sample this should work:

=Avg(Aggr(Avg([Ship-Date] - [Receipt-Date]), Batch#))

jonathandienst
Partner
Partner

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#])

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Tsk all - I think all seem to work, but being lazy I took the last reply - tks everyone for your help