9 Replies Latest reply: Oct 9, 2017 3:59 AM by Johnni Kirstein RSS

    Date Variable

    Johnni Kirstein

      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
          P M

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

            • Re: Date Variable
              Johnni Kirstein

              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

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

                    • Re: Date Variable
                      Johnni Kirstein

                      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

                          hi

                           

                          you can use interval though.

                           

                          regards

                          Pradosh

                            • Re: Date Variable
                              Johnni Kirstein

                              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

                                  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
                                pradosh thakur

                                HI

                                 

                                I guess you missed quotes in the date_months_2

                                 

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

                                 

                                regards

                                Pradosh