Max Date and the Second Max Date

Hi All,

I have my data with an ID and each ID has got multiple dates and valuation for each date. My requirement is to show the latest valuation and the previous valuation.

ID Date Value
1 31-03-2021 1000
2 31-03-2021 500
3 31-03-2021 400
1 30-06-2021 1100
2 30-06-2021 550
1 30-07-2021 1200
2 30-07-2021 600
4 30-06-2021 1000
4 30-07-2021 1050

  measure: latest_date {
    type: date
    sql: MAX(${date_raw}) ;;
    convert_tz: no
  }

This gave me max date for each ID. Now I need to know the value for the previous date. Need help in getting the Max-1 date.
 

ID Max Date Value Max-1 Date Value
1 30-07-2021 1200 30-06-2021 1100
2 30-07-2021 600 30-06-2021 550
3 31-03-2021 400 NA NA
4 30-06-2021 1000 30-07-2021 1050
         
         
         


The required output will be something like above. 

Regards,

Keerthi
 

2 1 472
1 REPLY 1

Any help on the above question?

Top Labels in this Space
Top Solution Authors