topic Overdue delivery date in QlikView

Overdue delivery date

Thu, 03 Mar 2011 16:24:26 GMT

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

Overdue delivery date

mazacini — Thu, 03 Mar 2011 16:38:48 GMT

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

Overdue delivery date

Miguel_Angel_Baeyens — Thu, 03 Mar 2011 16:41:02 GMT

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

Overdue delivery date

Fri, 04 Mar 2011 10:11:41 GMT

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