Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
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

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 II
Creator II

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

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

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

Hello Peter,

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

Peter_Cammaert

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

divya_anand
Creator II
Creator II

I agree with Peter, or you may also try

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

This might work as well.

Not applicable

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

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

Thank you Peter and Divya for your help