topic Re: Date Variable in QlikView

Date Variable

Anonymous — Fri, 06 Oct 2017 06:06:57 GMT

Hi Qlik Community.

I have a little issue regarding Variables when they are used with dates. Please see my attached qvw-file.

What I want to do is calculate the number of days between two dates. The dates are End_time and Date with two months added.

As you can see I have floored the End_time in the variable called v_PS.DWHDATE.Floor. Afterwards I make my calculation in the Orders table where I substract the variable from the Date with two months added (the Date_Months2 field).

In the chart my Date-Months2 show other values that I want to. The values should be the same as in the ShouldBe field. When I calculate the days in Excel I figured out that the calculation in the Date_Months2 field is rather: Date + 2 months, then minus 7 minus 7 minus 2017. It substracts 2031 from the Date + 2 months.

Instead I created the field Date_Months2Num based on numeric values from the dates. It gives me the correct results.

Can some of you tell me what is wrong with my field calculation in the Date_Months2 field and maybe explain how the varible should be made instead?

Thank you all in advance.

Best Regards,

Johnni Kirstein

Re: Date Variable

prma7799 — Fri, 06 Oct 2017 06:11:34 GMT

Where is the attachment? and what is your expected output.

Re: Date Variable

Anonymous — Fri, 06 Oct 2017 06:23:35 GMT

Hi P M.

Something went wrong when I started the post but now my question and attached file is there ;o))

Re: Date Variable

pradosh_thakur — Fri, 06 Oct 2017 13:28:04 GMT

did you try datediff() or interval() instead of minus(-) ?

Re: Date Variable

Anonymous — Mon, 09 Oct 2017 06:40:57 GMT

Hi Pradosh.

Thank you for your answer.

As I know the datediff() is a SQL function, not a Qlik function, so I am not able to use it in my script.

Re: Date Variable

pradosh_thakur — Mon, 09 Oct 2017 06:46:25 GMT

you can use interval though.

regards

Pradosh

Re: Date Variable

Anonymous — Mon, 09 Oct 2017 06:58:33 GMT

I seem like the interval is usefull if I do not use the variable but inserts the expression in the function instead. Below you can see my script. TEST2 gives me the correct result, not TEST1, but I want to use the variable v_PS.DWHDATE.Floor as a solution.

So what is missing?

Date_Time:

LOAD * INLINE [

End_time

07-07-2017 21:08:55

];

Let v_PS.DWHTIMESTAMP = Peek('End_time',0,'Date_Time');

Let v_PS.DWHDATE = Date(Peek('End_time',0,'Date_Time'));

Let v_PS.DWHDATE.Floor = Date(Floor(Peek('End_time',0,'Date_Time')));

Orders_tmp:

LOAD * INLINE [

No, Date, ShouldBe

1, 06-07-2017, 61

2, 01-06-2017, 25

3, 28-06-2017, 52

];

Orders:

LOAD No

,Date

,ShouldBe

,Num(Date) as Num_Date

,AddMonths(Date, 2) as ADD_2_MONTHS

,'$(v_PS.DWHTIMESTAMP)' as DWHTIMESTAMP

,$(v_PS.DWHDATE) as DWHDATE

,$(v_PS.DWHDATE.Floor) as DWHDATE_Floor

,AddMonths(Date, 2) - $(v_PS.DWHDATE.Floor) as Date_Months2

,Interval(AddMonths(Date, 2) - $(v_PS.DWHDATE.Floor), 'D') as TEST1

,Interval(AddMonths(Date, 2) - Date(Floor(Peek('End_time',0,'Date_Time'))), 'D') as TEST2

Resident Orders_tmp;

DROP Table Orders_tmp;

Re: Date Variable

pradosh_thakur — Mon, 09 Oct 2017 07:17:54 GMT

I guess you missed quotes in the date_months_2

AddMonths(Date, 2) - '$(v_PS.DWHDATE.Floor)' as Date_Months2

regards

Pradosh

Re: Date Variable

pradosh_thakur — Mon, 09 Oct 2017 07:27:05 GMT

something like this

Date_Time:

LOAD * INLINE [

End_time

07-07-2017 21:08:55

];

Let v_PS.DWHTIMESTAMP = Peek('End_time',0,'Date_Time');

Let v_PS.DWHDATE = Date(Peek('End_time',0,'Date_Time'));

Let v_PS.DWHDATE.Floor = Date(Floor(Peek('End_time',0,'Date_Time')));

Orders_tmp:

LOAD * INLINE [

No, Date, ShouldBe

1, 06-07-2017, 61

2, 01-06-2017, 25

3, 28-06-2017, 52

];

Orders:

LOAD No

,Date

,ShouldBe

,Num(Date) as Num_Date

,AddMonths(Date, 2) as ADD_2_MONTHS

,'$(v_PS.DWHTIMESTAMP)' as DWHTIMESTAMP

,$(v_PS.DWHDATE) as DWHDATE

,$(v_PS.DWHDATE.Floor) as DWHDATE_Floor

,AddMonths(Date, 2) - '$(v_PS.DWHDATE.Floor)' as Date_Months2

,Interval(AddMonths(Date, 2) - '$(v_PS.DWHDATE.Floor)', 'D') as TEST1

,Interval(AddMonths(Date, 2) - Date(Floor(Peek('End_time',0,'Date_Time'))), 'D') as TEST2

Resident Orders_tmp;

DROP Table Orders_tmp;

regards

Pradosh

Re: Date Variable

Anonymous — Mon, 09 Oct 2017 07:59:45 GMT

Hi Pradosh.

Finally. Thank you very much. The quotes did it.

Best Regards,

Johnni