Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
HI
I guess you missed quotes in the date_months_2
AddMonths(Date, 2) - '$(v_PS.DWHDATE.Floor)' as Date_Months2
regards
Pradosh
Where is the attachment? and what is your expected output.
Hi P M.
Something went wrong when I started the post but now my question and attached file is there ;o))
did you try datediff() or interval() instead of minus(-) ?
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.
hi
you can use interval though.
regards
Pradosh
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;
HI
I guess you missed quotes in the date_months_2
AddMonths(Date, 2) - '$(v_PS.DWHDATE.Floor)' as Date_Months2
regards
Pradosh
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
Hi Pradosh.
Finally. Thank you very much. The quotes did it.
Best Regards,
Johnni