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

    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