7 Replies Latest reply: Jan 16, 2018 3:58 AM by Peter Cammaert RSS

    Date function not functioning properly

    Saksham Kaul

      Hi

      when i am seeing my sold_date field in my dasboard its giving correct values of date till 2017 but not for 2018(attached image)

      why? When I am selecting sold_date the format for 2018 date is changed and sold_year field which is derived from sold_date is not updated with date 2018 why?

      this is a following script:-

      OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=ETPEAS;Data Source=192.168.1.70;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=QLIKVIEW;Use Encryption for Data=False;Tag with column collation when possible=False](XPassword is XEDJTZFMLB);

      Increment_Sales:
      Concatenate(POS_Master_final)
      Load
      Warehouse as ETPCode,
      InvoiceNumber as [inv no],
      Date#(CreateDate, 'YYYYMMDD') as Sold_Date,
      month(Date#(CreateDate, 'YYYYMMDD')) as Sold_Month,
      year(Date#(CreateDate, 'YYYYMMDD')) as Sold_Year,
      replace(ItemNumber,' ','') as itemnumber,
      left(ItemNumber,3) as [Season Code],
      InvoiceQuantity as quantity_sold,
      SalesPrice as [sales price],
      LocalAmount as [inv value],
      if ( LocalAmount <500, '<500', if ( LocalAmount <800, '500-799',if ( LocalAmount <1200, '800-1199',if ( LocalAmount <1600,'1200-1599',if ( LocalAmount <2500,'1600-2499',if ( LocalAmount <3500,'2500-3499',if ( LocalAmount <5000,'3500-4999','>=5000'))))))) as [Inv val Bucket],
      VatAmount as [tax amount];
      SQL SELECT *
      FROM ETPEAS.dbo.CashOrderTrn
      WHERE InvoiceType = '31' and (CreateDate >= 20130101 and CreateDate <= '$(currentdate)') ;

      Increment_Returns:
      Concatenate(POS_Master_final)
      LOAD
      Warehouse as ETPCode,
      SalesReturnNumber as [return no],
      Date#(CreateDate, 'YYYYMMDD') as Sold_Date,
      month(Date#(CreateDate, 'YYYYMMDD')) as Sold_Month,
      year(Date#(CreateDate, 'YYYYMMDD')) as Sold_Year,
      replace(ReturnItemNumber,' ','') as itemnumber,
      left(ReturnItemNumber,3) as [Season Code],
      ReturnQuantity as quantity_returns,
      SalesPrice as [sales price],
      -
      LocalAmount as [inv value],
      if ( LocalAmount <500, '<500', if ( LocalAmount <800, '500-799',if ( LocalAmount <1200, '800-1199',if ( LocalAmount <1600,'1200-1599',if ( LocalAmount <2500,'1600-2499',if ( LocalAmount <3500,'2500-3499',if ( LocalAmount <5000,'3500-4999','>=5000'))))))) as [Inv val Bucket];
      SQL SELECT *
      FROM ETPEAS.dbo.SalesReturnTrn
      WHERE InvoiceType = '31' and (CreateDate >= 20130101 and CreateDate <= '$(currentdate)') ;

      master calender


      for i=2013 to 2018

      for j=1 to 12

      step1:

      LOAD

      RecNo(),

      MakeDate($(i),$(j),RecNo()) as Sold_Date

      AutoGenerate(31);

      NEXT j;

      NEXT i;

      step2:

      LOAD Sold_Date,

      year(Sold_Date) as year,

      month(Sold_Date) as Month,

      Date(monthstart(Sold_Date), 'MM-YYYY') as MonthYear,

      day(Sold_Date) as Day,

      QuarterName(Sold_Date) as Quater1,

      WeekDay(Sold_Date) as weekday,

      if(Ceil(Month(Sold_Date)/3)=1,'Q1',IF(Ceil(Month(Sold_Date)/3)=2,'Q2',IF(Ceil(Month(Sold_Date)/3)=3,'Q3',IF(Ceil(Month(Sold_Date)/3)=4,'Q4')))) as Quarter


      date(Sold_Date) as OrderDate

      Resident step1;

      drop Table step1;


        • Re: Date function not functioning properly
          Clever Anjos

          How do you calculate currentdate?

          • Re: Date function not functioning properly
            Anil Babu

            Perhaps this?

             

            Alt(Date(Date#(CreateDate, 'YYYYMMDD')), Date(Num(CreateDate))) as Sold_Date,

            • Re: Date function not functioning properly
              Kamal Naithani

              Hi Saksham,

               

              Is your Sold_Date in Fact table giving the correct value.

               

              Regards

              Kamal

              • Re: Date function not functioning properly
                Colin Albert

                Have a look at this post on dates. The Date Function

                You may want to use Date(Date#(....)) to ensure your dates are processed consistently.

                 

                e.g. In Increment Returns

                 

                change the line          Date#(CreateDate, 'YYYYMMDD') as Sold_Date,

                to                                 Date(Date#(CreateDate, 'YYYYMMDD')) as Sold_Date,

                  • Re: Date function not functioning properly
                    Saksham Kaul

                    Here currentdate is not working in script

                    painterrrrrooorrrr.png

                     

                     

                     

                     

                     

                     

                     

                     


                    Incremental_Non_Etp_Returns:

                    Concatenate(POS_Master_final)

                    Load

                    //ApplyMap('SAPCODE_ETPCodeMapping',NCode,'') as ETPCode,

                       NCode as SAPCode,

                        InvoiceNumber as [return no],

                        Date(SalesDate, 'YYYYMMDD') as Sold_Date,

                        month(Date#(SalesDate, 'YYYYMMDD')) as Sold_Month,

                    year(Date#(SalesDate, 'YYYYMMDD')) as Sold_Year,

                     

                        replace(ItemNumber,' ','') as itemnumber,

                        left(ItemNumber,3) as [Season Code],

                        //'N_ETP SALE' as SaleType,

                        SaleQuantity as quantity_returns,

                        -NetValue as [inv value],

                        MRPValue as [sales price],

                        if ( NetValue <500, '<500', if ( NetValue <800, '500-799',if ( NetValue <1200, '800-1199',if ( NetValue <1600,'1200-1599',if ( NetValue <2500,'1600-2499',if ( NetValue <3500,'2500-3499',if ( NetValue <5000,'3500-4999','>=5000'))))))) as [Inv val Bucket],

                        -TaxAmount as [tax amount];


                    SQl Select WH.GoodsSender as NCode, SD.InvoiceNumber, SD.SalesDate,SD.ItemNumber,

                    SD.SaleQuantity, SD.NetValue,SD.MRPValue,SD.TaxAmount

                    FROM SAP_Link.dbo.Sales_DataNonETP_BIReports SD

                    INNER JOIN ETPEASV55.dbo.warehouse_sap_bireport WH

                    ON SD.CustomerId=WH.Warehouse

                    where SD.Doc_Type = 'ZIKR' and SD.SalesDate >= 20130101 and SD.SalesDate <= '$(currentdate)' ;

                      • Re: Date function not functioning properly
                        Peter Cammaert

                        That is because the $-sign substitution places the QlikView expression in a SQL SELECT statement instead of the calculated value. That won't work as most if not all RDBMS do not understand QlikView functions.

                         

                        It seems that you stuffed the string "= Date(Today()-1, 'YYYYMMDD')" in variable currentdate, instead of a value like "20180114" (yesterdays date). Are you sure the answer you posted to Clevers question is correct? It doesn't look like it.

                         

                        Check the current value of currentdate in Settings->Variable overview. What script statement did you really use to initialise that variable? Also check whether there aren't multiple statements that try to set currentdate.