topic Identify product sales made >90 days after delivery in QlikView

Identify product sales made >90 days after delivery

ingoniclas — Wed, 06 Nov 2013 18:06:52 GMT

I have the following challenge: I deliver product A to retail stores. Each retail store gets a 10% rebate back from me for every product sold. However, they retail stores sometimes confuse my product A with product B from another company and incorrectly claim rebates from me. In order to see which claims are incorrect I want to make use of the fact that my product A is only good for 90 days after delivery. In order to check on that I want to link my table with the records of my deliveries with each retailers' sales report via the product ID and automatically identify sales made >90 days after my last delivery. How can I achieve this? See an example below:

table with my deliveries to the wholesaler:

Product ID Delivery date Price

A 01/01/2012 20 $

A 01/05/2012 20 $

report of the wholesaler to claim his rebates:

Product ID Sales date Claimed rebate

A 02/15/2012 2 $ -> correct

A 04/10/2012 2 $ -> incorrect, no rebate to be disbursed since it was sold > 90 days after my last delivery. It can't be my product A.

Many thanks for any help.

Re: Identify product sales made >90 days after delivery

qliksus — Wed, 06 Nov 2013 22:10:22 GMT

Hi,

I don't know about ur data model, sorry in advance if i repeat something which you have already tried.

You need to design your data model such a way it looks like (IF I UNDERSTOOD UR BUSINESS LOGIC)

Prod Id DelDtae SalDate

A 01/01/2012 02/15/2012

A 01/05/2012 04/10/2012

Above is required if you have primary key as Product Id, if you have diff primary key then use that.

if you have primary key is Product Id, then you can achieve by using MAX, MIN functions

Re: Identify product sales made >90 days after delivery

ingoniclas — Thu, 07 Nov 2013 08:21:23 GMT

Thanks for your idea. However, this is not exactly what I need. I need a system that checks if an apparent sale of my product A as reported by a retailer actually could have taken place. I want realize this by looking up if up to 90 days earlier I actually supplied this product A. And this can not be solved by simply using MIN or MAX since sometimes I want to analyze data spanning many, many months.