3 Replies Latest reply: Oct 15, 2015 3:11 AM by Sunny Talwar RSS

    Filter by last 2 records

      Hello,

       

      I'm trying to make a bar chart that will make the calculation of time per category (lead time) for only the last two orders.

       

      This is my raw data...

       

      CATEGORYLEADTIMEFINISH DATE
      Productos Clientes82015-09-29 00:00:00
      Productos Clientes62015-09-29 00:00:00
      Productos Clientes62015-09-29 00:00:00
      Especiales12015-09-28 00:00:00
      Especiales52015-09-27 00:00:00
      Productos Clientes12015-09-27 00:00:00
      Productos Clientes12015-09-26 00:00:00
      Especiales12015-09-26 00:00:00
      Productos Clientes52015-09-25 00:00:00

      and I expect this

       

      CATEGORY               AVERAGE(LAST 2 ORDERS)

      Productos Clientes        7   ((8+6)/2)

      Especiales                    3   ((1+5)/2)

       

      And then use this for fill a bar chart, so I think the expression would be in the fx for the measure like a subset filtering of the data (Mysql: Sort and Limit)

       

      Thanks in advance!

        • Re: Filter by last 2 records
          Sunny Talwar

          May be create a flag in the script:

           

          Table:

          LOAD CATEGORY,

            LEADTIME,

            Date(Floor(TimeStamp#([FINISH DATE], 'YYYY-MM-DD hh:mm:ss'))) as [FINISH DATE],

            RowNo() as SNo;

          LOAD * Inline [

          CATEGORY, LEADTIME, FINISH DATE

          Productos Clientes, 8, 2015-09-29 00:00:00

          Productos Clientes, 6, 2015-09-29 00:00:00

          Productos Clientes, 6, 2015-09-29 00:00:00

          Especiales, 1, 2015-09-28 00:00:00

          Especiales, 5, 2015-09-27 00:00:00

          Productos Clientes, 1, 2015-09-27 00:00:00

          Productos Clientes, 1, 2015-09-26 00:00:00

          Especiales, 1, 2015-09-26 00:00:00

          Productos Clientes, 5, 2015-09-25 00:00:00

          ];

           

          Temp:

          LOAD CATEGORY,

            FirstSortedValue([FINISH DATE], SNo) as [FINISH DATE],

            FirstSortedValue(LEADTIME, SNo) as LEADTIME,

            FirstSortedValue(SNo, SNo) as SNo,

            1 as Flag

          Resident Table

          Group By CATEGORY;

           

          Concatenate(Temp)

          LOAD CATEGORY,

            FirstSortedValue([FINISH DATE], SNo, 2) as [FINISH DATE],

            FirstSortedValue(LEADTIME, SNo, 2) as LEADTIME,

            FirstSortedValue(SNo, SNo, 2) as SNo,

            1 as Flag

          Resident Table

          Group By CATEGORY;

           

          Join(Table)

          LOAD *

          Resident Temp;

           

          DROP Table Temp;

           

          On the front end create a straight table with the following:

           

          Dimension: CATEGORY

          Expression: =Avg({<Flag = {1}>}LEADTIME)

           

          Output:

          Capture.PNG

          • Re: Filter by last 2 records

            Thanks Sunny, I really apreciate your answer.

             

            In the end, I managed to work into the dimension expression of a bar-chart, with the whole original table.

             

            Thereby, I used the FirstSortedValue twice for extract the last and before last values and RangeAvg to show their mean measure.