topic Re: Calculated Dimension / Time Series before Event in QlikView

Calculated Dimension / Time Series before Event

Wed, 01 May 2013 21:04:15 GMT

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

Re: Calculated Dimension / Time Series before Event

Gysbert_Wassenaar — Thu, 02 May 2013 13:11:54 GMT

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

Sat, 11 May 2013 17:02:06 GMT

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({$<Datex={'3'}>} 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

swuehl — Sat, 11 May 2013 22:32:10 GMT

Maybe like attached?

Re: Calculated Dimension / Time Series before Event

Mon, 13 May 2013 04:42:27 GMT

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

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

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

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