Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

vMindate adding one more day

Hello all,

in my report I have

temp:

     NoConcatenate

     LOAD

          distinct min (date_sale) as MinDate;

     SQL

          Select

          date_sale

      From my.table

let vMinDate = num ('2015-01-01')

but in the report the min date does not start from 2015-01-01 but it starts from 2015-02-01 so it adds one more day, for testing purpose I changed to let vMinDate = num ('2014-31-12'), then it starts from 2015-01-01.


Would you please advice on this and if this is how Qlikview works?

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

There you have it. In your sample script you don't seem to be using vMinDate for anything else, so try with the following code:

:

let vMinDate = '2015-01-01';

let vMaxDate = num(peek('max_date',-1,'temp'));

let vToday =  num(peek('max_date',-1,'temp'));

drop table temp;

calendar_table:

  load

  date_sale as date_of_sale;

SQL

  Select

  date_sale

  From  calendar

  Where

  date_sale >='$(vMinDate)';

View solution in original post

9 Replies
divya_anand
Creator III
Creator III

Hi Amir,

Are you trying to use vMinDate for a master calendar? I do not understand the purpose of variable "vMinDate", what is the script after "let vMinDate = num ('2015-01-01')"?

Would be easier if you can share a sample QVW.

Not applicable
Author

Hello Divya,

In my main sql calendar I have all dates, but for this report I want the date to start from 2015-01-01, the use of vMindate is that I will use this variable in different areas in my report.

This is sample of my script

temp:

  NoConcatenate

  LOAD

  distinct min (date_sale) as MinDate;

;

  SQL

  Select

  date_sale

  From

calendar

;

let vMinDate = num('2015-01-01');

let vMaxDate = num(peek('max_date',-1,'temp'));

let vToday =   num(peek('max_date',-1,'temp'));

drop table temp;

calendar_table:

  load

  date_sale as date_of_sale;

SQL

  Select

  date_sale

  From   calendar

  Where

  date_sale >=$(vMinDate);

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Check the value of variable vMinDate. It's a number (probably 42005), correct?

Is your RDBMS able to understand what this number means? Did you try with a WHERE clause like

:

WHERE date_sale >= '2015-01-01';

Not applicable
Author

Hello Peter,

Yes I tried it with Where clause and it accepts it perfectly.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

There you have it. In your sample script you don't seem to be using vMinDate for anything else, so try with the following code:

:

let vMinDate = '2015-01-01';

let vMaxDate = num(peek('max_date',-1,'temp'));

let vToday =  num(peek('max_date',-1,'temp'));

drop table temp;

calendar_table:

  load

  date_sale as date_of_sale;

SQL

  Select

  date_sale

  From  calendar

  Where

  date_sale >='$(vMinDate)';

divya_anand
Creator III
Creator III

I agree with Peter, or you may also try

let vMinDate = floor('2015-01-01');

This might work as well.

Not applicable
Author

Hi All,

One more thing to add,

If I my data is coming from SQL load, the date_sale starts from 2015-02-01 adding one more day, but if my data is coming from QVD then the date starts correct from 2015-01-01

qvd script

temp:

  NoConcatenate

  LOAD

  distinct min (date_sale) as MinDate;

FROM

  [$(vFileLocation)calendar.qvd](qvd);

;

let vMinDate = num('2015-01-01');

let vMaxDate = num(peek('max_date',-1,'temp'));

let vToday =   num(peek('max_date',-1,'temp'));

drop table temp;

 

calendar_table:

  load

  date_sale as date_of_sale;

   From   [$(vFileLocation)calendar.qvd](qvd);

  Where

  date_sale >=$(vMinDate);

I am really confused here,

Peter_Cammaert
Partner - Champion III
Partner - Champion III

That is because date fields in a QVD are QlikView dual fields. Dual values store a string representation (in your case probably like YYYY-MM-DD) together with a binary value à la Excel. The binary value for '2015-01-01 00:00:00' is 42005.

AFAIK there aren't a lot of RDBMS that understand Excel binary date values which you get if you keep using the num() function to drop the text representation. But they all understand dates in Database Format, meaning YYYY-MM-DD.

Not applicable
Author

Thank you Peter and Divya for your help