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
Any help on the above question?