2 Replies Latest reply: Feb 18, 2016 8:12 PM by CIRO ORIC RSS

    Using FirstSorteValue Inside Above function - How to?

    CIRO ORIC

      Hi there!

       

      I have a table with quotations of price, like:

      So, I create a table to show only the last price for each month like (most recent for each month):

      Now, for each month, I'd like to show the previous cote, so I add the following column:

       

      But there is an issue in my approach: when the year change, using Above function, I can't get the previous value.

      Is there a way to fix it?

       

      To generate the data serie, I used the following script:

       

      temp:

        LOAD * inline [

        Date_02 Values_02

        '04/01/11' 32

        '05/01/11' 24

        '24/05/11' 27

        '01/10/11' 43

        '04/10/11' 46

        '09/11/11' 33

        '11/02/12' 46

        '23/02/12' 36

        '22/05/12' 43

        '19/06/12' 24

        '11/08/12' 46

        '08/12/12' 20

        '30/12/13' 39

        '04/09/13' 23

        '15/09/13' 32

        '07/11/13' 20

        '24/11/14' 45

        '07/12/14' 22

        '24/12/14' 42

        '07/01/14' 20

        '24/01/14' 42

        '10/02/14' 17

        '18/02/14' 42

      ] (delimiter is ' ');

       

      SalesSerie_01:

           Load Date_02 as Date,

               Year(Date_02) as Year,

               Month(Date_02) as Month,       

               Date(MonthStart(Date_02),'YYYY MMM') as YearMonth, 

               Values_02 as [Price Cote]

          Resident temp;

         

      Drop table temp;

       

      Best regards,