Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
ingoniclas
Contributor

Identify product sales made >90 days after delivery

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.

Tags (1)
2 Replies
qliksus
Valued Contributor

Re: Identify product sales made >90 days after delivery

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

ingoniclas
Contributor

Re: Identify product sales made >90 days after delivery

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.

Community Browser