4 Replies Latest reply: Jul 10, 2013 10:54 PM by Sokkorn Cheav RSS

    help Needed

      hi,

         Im having a problem in script to find ,

         Orders within last 30 Days,

         Orders between 31-60 days,

          

      LOAD OrderID,

           CustomerID,

           EmployeeID,

           OrderDate,

           Year(OrderDate) as Year,

           Month(OrderDate) as  Month,

           Week(OrderDate) as Week,

           If( (Today() - OrderDate)<31, ORDER) as Last30DaysOrded,

           If( (Today() - OrderDate)>30  AND (Today() - OrderDate)>61,ORDER) as 31to60DayOrded,   

           RequiredDate,

           ShippedDate,

           ShipVia,

           Freight,

           ShipName,

           ShipAddress,

           ShipCity,

           ShipRegion,

           ShipPostalCode,

           ShipCountry

                           This is my script , can anyone help for me .

        • Re: help Needed
          Alessandro Saccone

          Probably dates have not the same format, try with date(Today(),'dd/MM/yyyy'), date(OrderDate,'dd/MM/yyyy')

          • Re: help Needed
            diego GOmez

            LOAD OrderID,

                 CustomerID,

                 EmployeeID,

                 OrderDate,

                 Year(OrderDate) as Year,

                 Month(OrderDate) as  Month,

                 Week(OrderDate) as Week,

                 If( (Today() - OrderDate)<31, ORDER) as Last30DaysOrded,

                 If( (Today() - OrderDate)>30  AND (Today() - OrderDate) < 61,ORDER) as 31to60DayOrded,  

                 RequiredDate,

                 ShippedDate,

                 ShipVia,

                 Freight,

                 ShipName,

                 ShipAddress,

                 ShipCity,

                 ShipRegion,

                 ShipPostalCode,

                 ShipCountry

            • Re: help Needed
              Sokkorn Cheav

              binodkoirala wrote:

                   If( (Today() - OrderDate)<31, ORDER) as Last30DaysOrded,

                   If( (Today() - OrderDate)>30  AND (Today() - OrderDate)>61,ORDER) as 31to60DayOrded,

              You want to find order base on period. Look at your If() statement; found something wrong. See you return "ORDER" with both If() statement; that is logic error.

               

              Here is my suggestion is create a flag for each period, something like:

              Load

              ...

              ORDER, //Assum this field is your order amount

              If(Today()-OrderDate<31, '0-30') AS Last30DaysOrded,

              If(Today()-OrderDate)<61,'31-60') AS 31to60DayOrded,

              ...

               

              Then if you want to sum order in last 30 day, we can use =Sum({$<Last30DaysOrded = {'0-30'}>} ORDER)

               

              Regards,

              Sokkorn