topic Re: vMindate adding one more day in QlikView

vMindate adding one more day

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: vMindate adding one more day

divya_anand — Mon, 12 Jun 2017 08:40:05 GMT

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.

Re: vMindate adding one more day

Mon, 12 Jun 2017 08:56:22 GMT

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);

Re: vMindate adding one more day

Peter_Cammaert — Mon, 12 Jun 2017 09:02:51 GMT

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';

Re: vMindate adding one more day

Mon, 12 Jun 2017 09:07:50 GMT

Hello Peter,

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

Re: vMindate adding one more day

Peter_Cammaert — Mon, 12 Jun 2017 09:15:51 GMT

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)';

Re: vMindate adding one more day

divya_anand — Mon, 12 Jun 2017 09:20:36 GMT

I agree with Peter, or you may also try

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

This might work as well.

Re: vMindate adding one more day

Mon, 12 Jun 2017 09:23:02 GMT

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,

Re: vMindate adding one more day

Peter_Cammaert — Mon, 12 Jun 2017 09:30:18 GMT

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.

Re: vMindate adding one more day

Mon, 12 Jun 2017 12:28:04 GMT

Thank you Peter and Divya for your help