Date Variable

[Anonymous]

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

[prma7799]

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

[Anonymous]

Hi P M.

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

[pradosh_thakur]

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

[Anonymous]

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.

[pradosh_thakur]

hi

you can use interval though.

regards

Pradosh

[Anonymous]

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;

[pradosh_thakur · Accepted solution]

HI

I guess you missed quotes in the date_months_2

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

regards

Pradosh

[pradosh_thakur]

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

[Anonymous]

Hi Pradosh.

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

Best Regards,

Johnni