Overdue delivery date

[#1]

Hello Qlikview community!

I'm trying to find a way to track down orders that have a specific requested delivery date and have a flag that says either open, delivered or invoiced.

As an example:

Order: 8888
Requested delivery: 31.01.2011 (DD.MM.YYYY)
Status: delivered

This is okay!

Order: 9999
Requested delivery: 31.01.2011 (DD.MM.YYYY)
Status: open

I would like to find all orders that have not been shipped (=open) with a delivery time that is more than 2 weeks past than todays date.

I would be really thankful for all suggestions!

Regards

Günter

[mazacini]

I'm sure there are a number of ways to do this.

At the basic level, if you create a simple table / chart of the data you want to display, then, using "Select Fields", display the Requested Delivery and Status fields, you can simply filter as required by highlighting "Open" Status, and Requested Delivery dates > 2 weeks old.

Alternatively, you can use Set Analysis in your expressions.

Hope this helps

Joe

[Miguel_Angel_Baeyens · Accepted solution]

Hello Günter,

If you have to do that in a chart (using Order number as dimension) the expression might look like

Count({< Status = {'open'}, [Requested Delivery] = {'>=$(=Date(Today() -14, 'DD.MM.YYYY'))'} >} Order)

Note that QlikView is case sensitive for both fieldnames and values!

You can flag them in the script either (which will likely perform better)

Table:LOAD ... IF(Status = 'open', If(Interval(Today() - Date#([Requested Delivery], 'DD.MM.YYYY')) >= 14, 1, 0)) AS TwoWeeksFlagFROM Source;

Hope that helps

[#4]

Hello Miguel,

thank you very much for your reply. I tried the first approach and it worked out just perfect. The only thing I had to change was the >= for the date to <= as I wanted all orders before that date.

Many thanks again for the quick and perfect help.

regards

Günter