I have a set of date with a number of entries per point in time, such as the following:
From a set of data (Country, Region, Date, Incidents), I need to identify for each country the point in time where the sum of incidents goes above a certain threshold (say 20) for the first time.
In our dataset, see below, this would be Date=3 for Country A, and Date=5 for Country B.
I'm interested in the development of the incidents before the threshold is hit, so I would like to see a chart with a time axis "normalized" to t=0 representing Date = 3 for Country A and Date = 5 for Country B.
Essentially I want a chart with one line per country, showing the values leading up to t0. Written as a table, this would look like this
| Country | t_normalized | Sum_Incidents |
|---|
| A | -2 (formerly 1) | 16 |
| A | -1 (formerly 2) | 19 |
| A | 0 (formerly 3) | 21 |
| B | -3 (formerly 2) | 3 |
| B | -2 (formerly 3) | 4 |
| B | -1 (formerly 4) | 3 |
| B | 0 (formerly 5) | 26 |
I've tried to solve this with value lists, dynamic dimensions, complex aggregation etc...no luck
How should I approach this problem?
Any help would be greatly appreciated,
Michael
Dataset:
| Country | Region | Date | Incicents |
|---|
| A | R1 | 1 | 5 |
| A | R2 | 1 | 6 |
| A | R3 | 1 | 5 |
| A | R2 | 2 | 9 |
| A | R3 | 2 | 10 |
| A | R2 | 3 | 12 |
| A | R3 | 3 | 9 |
| A | R4 | 4 | 8 |
| A | R1 | 4 | 11 |
| B | A1 | 2 | 1 |
| B | A2 | 2 | 1 |
| B | A3 | 2 | 1 |
| B | A2 | 3 | 2 |
| B | A3 | 3 | 2 |
| B | A3 | 4 | 2 |
| B | A2 | 4 | 1 |
| B | A1 | 5 | 9 |
| B | A2 | 5 | 8 |
| B | A3 | 5 | 9 |
| | | |
