How do you create a filter for a date field to bring in items older than today?

[#1]

Trying to pull in data for orders that are past due. I've tried the following filter and it does not appear to work:

OpenOrd Date Dlvry Req<num(today())

FYI I was pulling in invoices with an invoice date of yesterday and it worked with the following value:

=num(today()-1)

Thanks

[Gysbert_Wassenaar]

Can you explain what you're trying to do? It's unclear to me if you're asking about loading source data into Qlikview or about creating an expression for a chart. Perhaps you can post a small qlikview document that illustrates your problem.

[swuehl]

Could you specify what you mean with 'does not appear to work'?

No records fetched, also records fetched with today's date, etc?

As Gysbert said, it's unclear in which context you are trying to filter your data, is it in a LOAD script or chart?

From your expression

=num(today()-1)

I would assume that's in the context of a chart, but what are the dimensions then and how does your model look like?

In general, some hints you might follow:

• - You should quote your field references when they contain e.g. spaces:

          [OpenOrd Date Dlvry Req]

          or    

          "OpenOrd Date Dlvry Req"

QlikView Quoteology

• - When you are using [OpenOrd Date Dlvry Req] < num(today()) in a chart expression, consider using an aggregation function for your date!

Use Aggregation Functions!‌

• - Check that your date field shows a numeric representation:

Data Types in QlikView

Get the Dates Right

Why don’t my dates work?

• - When using today(), read about the (default) timer modes of today() function in the Help file: If you are using Today() in the script, I think it's better to use Today(1)

[Daniel_Jenkins]

Hi Christopher,

If your field name is OrderDate your Field type filter would be set up something like this:

HTH - Daniel.

[Anonymous]

What is the format of OpenOrd Date Dlvry Req?? Is it Number or in Date Format??

If it is Date Format field, then

With this

OpenOrd Date Dlvry Req<num(today())

You are comparing Date with number which is not compatible..

Make sure Format of OpenOrd Date Dlvry Req & today() should be same..

[#6]

Hi Balraj,

When I enter the following values(picture below), I can get orders that are 2,3,4 and etc days old:

But when I try what you recommended, it does not work and just pulls in every date possible. Sorry I'm pretty new to Qlikview and Nprinting so my explanations are probably not very detailed.

[Daniel_Jenkins · Accepted solution]

Hi Christopher,

Let's say you have this straight table in your QlikView document:

You can see that OpenOrd Date Dlvry Req spans dates before and after today (23rd Feb 2016). If you want a QlikView NPrinting report to be filtered for only the dates before today you can create a Field filter like this (I named it filter😞

Apply the filter to the report or chart and you will get the required result - all orders with OpenOrd Date Dlvry Req prior to today :

I have attached the project I used. Please extract to folder C:\Temp\Community\038 or change the paths in the NSQ as required.

HTH - Daniel.

[#8]

Thank you very much. Works perfect now!