topic Re: Date Variable in Set Analysis in QlikView

Date Variable in Set Analysis

Anonymous — Fri, 28 Nov 2014 08:29:00 GMT

I load my data from a MS Access Database. One of the fields is 'Shipmentdate'. I load this field in my script like this:

date(Shipmentdate) as Shipmentdate

I also create a variable, returning the date of yesterday:

LET vYesterday = date(Today()-1);

Now, I create a table, where I use this as expression:

=sum({$<Shipmentdate={'<=$(#=vYesterday)'} >} SHP)

This returns all zero's, whilst there is definitely date on yesterday.

Please find below 2 screenshots (to show you the outcome of my scripting):

Listbox of Shipmentdate:

Variable Overview:

Anybody have any idea what I'm doing wrong?

Thanks in advance for the help once again!

rubenmarin — Fri, 28 Nov 2014 08:36:38 GMT

Hi Stefan, can you try?:

=sum({$<Shipmentdate={'<=$(=vYesterday)'}>} SHP)

amit_saini — Fri, 28 Nov 2014 08:39:51 GMT

Stefan,

Try this:

sum({$<Shipmentdate={"<=$(=vYesterday)"} >}SHP)

Thanks,

Anonymous — Fri, 28 Nov 2014 08:44:18 GMT

Ruben,

thanks for the response. However, the formula suggested returns my SHP for the entire year, not only yesterday...

rubenmarin — Fri, 28 Nov 2014 08:46:58 GMT

You can change the compare from '<=' to '=', or remove it:

=sum({$<Shipmentdate={'$(=vYesterday)'}>} SHP)

Anonymous — Fri, 28 Nov 2014 08:52:18 GMT

Removing it did the trick indeed, thanks for the quick help!