9 Replies Latest reply: Dec 31, 2010 10:41 AM by SQView81 RSS

    Calendar problem

    SQView81

      Hi,
      I'm trying to create a calendar that ranges between the min (20101229)and max(20101230) dates I have in a table.
      But, the following code doesn't work:

       

      tmp:

      LOAD
      min(Date) as MinDate
      max(Date) as MaxDate
      RESIDENT Orders;
      LET Start = floor(YearStart(peek('MinDate')));
      LET End = floor(YearEnd(peek('MaxDate')));



      For some reason I get weird dates in Start and End variables.
      For ex. End gets '20101487' - What's wrong?

       

      Thanks



        • Calendar problem
          Miguel Angel Baeyens de Arce

          Hello,

          It's likely a conversion/formatting issue. Dates in QlikView are numeric values starting 31/12/1899 (Date 1). Try instead

           

          LOADDate#(min(Date), 'YYYYMMDD') as MinDateDate#(max(Date), 'YYYYMMDD') as MaxDateRESIDENT Orders;LET Start = floor(YearStart(peek('MinDate')));LET End = floor(YearEnd(peek('MaxDate')));


          Hope that helps

           

           

            • Calendar problem
              SQView81

              Yes, that was the problem. Thank you very much!!!

              • Calendar problem
                SQView81

                I'm going crazy I seem to have a problem again.

                For some reason the variabes start and end in my code don't contain the dates and nothing helps!
                (start and end variables should contain the starting and end dates, they contain weird numbers??..)

                I attached the ex. and the output-I'd appreciate any help..
                Thanks again

                  • Calendar problem

                    Hi ,

                    Try using Make date function.

                    Example

                    Load

                    Id,

                    Sale,

                    makedate(2010,01,01) as Period_date,

                    num(makedate(2010,01,01)) as Period_date_Num

                    from Orders;

                     

                    follow the steps

                    1)create 2 variables Vstart and Vend.

                    2)create calendar object, , In variables select Vstart, In Min values write exp Min(Period_date) and max values exp Max(Period_date)

                    3) create calendar object , follow step 2

                    4)Change format to date in Number Tab for both calendar object

                    5) Chart expression :

                    SUM(IF(Period_date_Num > Vstart AND Period_date_Num < Vend , Costs))

                     

                    you will get the range of cost between the selected dates in calendar.

                     

                    Regards,

                    Prabhu

                     

                     

                     

                     

                     

                    • Calendar problem
                      Miguel Angel Baeyens de Arce

                      Hello,

                      Make sure that the date format from the excel sheet is YYYYMMDD. You can check this piece of script, based on yours, that should work

                       

                      Data:LOAD * INLINE [OID, CID, DateA, 1, 20100101B, 2, 20100223C, 3, 20101205]; DateMinMax:LOAD Date(Date#(Max(Date), 'YYYYMMDD')) AS MaxDate, Date(Date#(Min(Date), 'YYYYMMDD')) AS MinDateRESIDENT Data; LET vMaxDate = Num(Peek('MaxDate', 0)); // This will return the date in QlikView numeric format 40517 which epoch is 1899/12/31LET vMinDate = Num(Peek('MinDate', 0)); // Use Date(vMinDate) in a text object and you should get the proper date representation DROP TABLE DateMinMax;


                      I've added a Date() representation function to the Date#() existing.

                      Hope this helps