'OBIEE getting the latest dimension column value based on time
Say that I have an analysis with the columns Month, Difficulty, and Score. The report has a filter Month = August, but the column Difficulty changes within the dates in August. For example, Difficulty = 'Hard' for 08/01/2021 and Difficulty = 'Easy' for 08/31/2021. How do I show only one row with the latest difficulty based on the time dimension, Month?
In this example, it should show only one row with Difficulty = 'Easy' and Score as the sum of all scores for the month instead of multiple rows every time that difficulty changes within the month
Instead of the following:
| Difficulty | Score |
|---|---|
| Hard | 5 |
| Easy | 7 |
It should show:
| Difficulty | Score |
|---|---|
| Easy | 12 |
Not sure if I can achieve this using selection steps. I tried setting the aggregation in the RPD for the Difficulty column to Last, but I don't think it works for non-measure columns.
Solution 1:[1]
Well, you should consider some cases. First of all, you should use the date format monthly in your analysis. (without day) Next, you should use some aggregation function. (either in RPD or analysis criteria) Last, you should sort your report according to date DESC also you can use that date column on your report and hide it. (in full date format)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Saeed J |