4 Replies Latest reply: May 13, 2013 12:42 AM by Michael Stöckli

# Calculated Dimension / Time Series before Event

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
Countryt_normalizedSum_Incidents
A-2 (formerly 1)16
A-1 (formerly 2)19
A0 (formerly 3)21
B-3 (formerly 2)3
B-2 (formerly 3)4
B-1 (formerly 4)3
B0 (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:
CountryRegionDateIncicents
AR115
AR216
AR315
AR229
AR3210
AR2312
AR339
AR448
AR1411
BA121
BA221
BA321
BA232
BA332
BA342
BA241
BA159
BA258
BA359
• ###### Re: Calculated Dimension / Time Series before Event

I don't think I can do this in one chart. I've put two on top of each other in the attached example. If you have more countries that is not going to be practical. It could be made to work for comparing countries pair-wise.

• ###### Re: Calculated Dimension / Time Series before Event

Thanks Gysbert

I think this should be possible though...meanwhile I got a step closer:

sum({\$<Datex={'\$(=min(aggr(if(aggr(sum(Incidents), Country, Datex)>20,Datex,10000),Country, Datex)))'}>} Incidents)

Provides some, put not all results, output looks like this:

 Country sum({\$} Incidents) A 21 B 4

What I don't get is why the output or the set modifier is evaluated only as '3', and not as '5' for country B....

Ideas anyone?

• ###### Re: Calculated Dimension / Time Series before Event

Maybe like attached?

• ###### Re: Calculated Dimension / Time Series before Event

Exactly like the attached. Thanks a lot - will try to understand the formula..:-)