Hello everyone,
I have a data in which I'm showing max date only but adding two additional calculated columns in table. These columns includes average value of max date and previous date of max date.
Table:
| ID |
Date |
Value |
| 800123 |
2/17/2021 |
4 |
| 800123 |
2/17/2021 |
3 |
| 800123 |
2/17/2021 |
2 |
| 800123 |
2/17/2021 |
4 |
| 800123 |
1/14/2021 |
5 |
| 800123 |
1/14/2021 |
2 |
| 800123 |
1/14/2021 |
3 |
| 800123 |
1/5/2021 |
4 |
| 800123 |
1/3/2021 |
2 |
Expected Result:
| ID |
Date |
Value |
Avg of maxdate(2/17/2021) |
Avg of P_date(1/14/2021) |
| 800123 |
2/17/2021 |
4 |
3.25 |
4 |
| 800123 |
2/17/2021 |
3 |
3.25 |
4 |
| 800123 |
2/17/2021 |
2 |
3.25 |
4 |
| 800123 |
2/17/2021 |
4 |
3.25 |
4 |
Issues facing while trying few things:
1) Not getting the average value of P_date in front of the max date
2) Not able to add average in set analysis with only condition
I have tried below things:
- only({<P_Date = Date >}value)
- avg({<P_Date= Date>}at_feedback_rating_id_1)
- avg( {$<Date = {$(#=Only(P_Date) = Date)}>} value)
