1 Reply Latest reply: Apr 3, 2012 11:36 AM by alejandro hernández RSS

    Help - Max date and date between

      Hi

      I want to know how to get the last or max date from atransaction for example

      I have the following data here’s an Inline but I’m readingfrom a database:

      Data:LOAD * INLINE [
          Product, Year_Month, Sales
          A, [2011 12 Jan], 1000
          A, [2012 13 Feb], 2000
          A, [2012 20 Mar], 3000   
          B, [2011 15 Jan], 4000
          B, [2012 10 Feb], 5000
          B, [2012 8 Mar], 6000
          C, [2011 2 Jan], 7000
          C, [2012 22 Feb], 8000
          C, [2010 11 Mar], 9000
      ]
      Group by Product;

       

      I want to get the max date by product for example and getthe date between Today and that date of the transaction.

      A, 2012 Mar, 3000  and the date now() – Year_Month

      B, 2012 Mar, 6000    andthe date now() – Year_Month

      C, 2012 Feb, 8000  and the date now() – Year_Month

       

      Thanks

      I will appreciate your help

       

      If need more info let me know

        • Help - Max date and date between

          Data:

          LOAD Product, date(date#(Year_Month,'yyyy dd MMM')) as Year_Month, Sales INLINE [

          Product, Year_Month, Sales

          A, '2011 12 Jan', 1000

          A, '2012 13 Feb', 2000

          A, '2012 20 Mar', 3000   

          B, '2011 15 Jan', 4000

          B, '2012 10 Feb', 5000

          B, '2012 8 Mar', 6000

          C, '2011 2 Jan', 7000

          C, '2012 22 Feb', 8000

          C, '2010 11 Mar', 9000

          ];

           

           

          Totals:

          Load *, Now()-MaxDate as DaysSince;

          Load date(max(Year_Month)) as MaxDate, Product as TheProduct resident Data Group by Product;