Here is the simple table data I have:
What I want:
For each business, I am only interested in it's LATEST Profit% in QuickSight table format (or any other visual type honestly and not just table)
What I have tried:
I put this table into QuickSight's Table visual type and put the "Business Name" into "Group by", "Timestamp" and "Profit%" into "Value". Fortunately, I can select only the max value of the Timestamp column (which is exactly what I want!) as you can see in the image below:
The first two columns are exactly what I want however if you look at the "Profit%" column, you can see that I am getting a "Sum" value. But I just want the respective value associated with that respective "Max" Timestamp only! For example for the "Business-1" row, the expected value in the Profit% column is 25.
Possible solutions I have tried:
i. Using a calculated field using the lastValue function - Unfortunately, this function is not supported in the region I am using QuickSight. If it was supported, my issue would have been resolved.
ii. Using a calculated field called rank using the function rank and then using a filter for this column. This has not worked out as yet. Suggestions are welcome if it is actually possible using this logic.
I think you will be able to get the desired result by using the maxover function and an ifelse function ifelse(Timestamp=maxover(Timestamp,[{Business Name}],PRE_FILTER),{Profit%},0)