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?
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?
LOAD * INLINE [
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')));
LOAD * INLINE [
No, Date, ShouldBe
1, 06-07-2017, 61
2, 01-06-2017, 25
3, 28-06-2017, 52
,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