topic Using a Date variable in an expression in QlikView

Using a Date variable in an expression

Tue, 13 May 2014 12:50:51 GMT

Hi All,

Hope you can help with a problem I am struggling with.

I have an expression:

=Sum( {<ForecastVersion = {$(vLast_Forecast)}, ShipDate = {$(vMax_Date)}, Measure = {'Available Amount'}>} Data)

This works fine when I use this the date hardwired in as follows:

=Sum( {<ForecastVersion = {$(vLast_Forecast)}, ShipDate = {'201710'}, Measure = {'Available Amount'}>} Data)

But when I use ShipDate = vMax_Date I don't get any output. the variable is defined as: Max(Date(Date# (ShipDate , 'YYYYMM')))

I've racked my head with this but no success... any help appreciated..

Regards

John

Re: Using a Date variable in an expression

its_anandrjs — Tue, 13 May 2014 12:56:29 GMT

Try with this

=Sum( {<ForecastVersion = {'$(vLast_Forecast)'}, ShipDate = {'$(vMax_Date)'}, Measure = {'Available Amount'}>} Data)

=Sum( {<ForecastVersion = {"$(vLast_Forecast)"}, ShipDate = {"$(vMax_Date)"}, Measure = {'Available Amount'}>} Data)

Or check your date format of the variable vMax_Date for this expression Max(Date(Date# (ShipDate , 'YYYYMM')))

Re: Using a Date variable in an expression

swuehl — Tue, 13 May 2014 13:21:32 GMT

You are interpreting your ShipDate field using date#() function, which will create a numeric representation of > 40000 (interpretating the year month field as date).

Then you aggregate to the max, but the resulting number probably doesn't match your original field format.

Try just a max() on your your field for the variable definition:

=max(ShipDate)

edit: Or, if ShipDate is in fact a formatted date itself, use something like

=Date(max(ShipDate), 'YYYYMM')

another add-it:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work