Hi Experts,
Kindly help me to get only record with max ( Prod_id) only if TransDt is same from below table :
| ID |
Desc |
Prod_id |
TransDt |
Count |
| 212 |
Category1 |
818 |
8/8/2021 |
1 |
| 212 |
Category2 |
816 |
8/8/2021 |
1 |
| 212 |
Category2 |
712 |
7/1/2021 |
|
Expected o/p:
| ID |
Desc |
Prod_id |
TransDt |
Count |
| 212 |
Category1 |
818 |
8/8/2021 |
1 |
This is current formula for calculating count:
Count({Distinct if( aggr(distinct max(TOTAL TransDt, Prod_id), Desc, ID)=
TransDt,ID))
This is expression is correct when TransDt is same with same Desc, it is showing max(Prod_id) count but
when TransDt is same and different Desc, it is showing me 1,1 separate count for each Desc so in this situation we want to consider only the records with prod_id is max.
Kinly help
Mahamed
