Aggr in pivot table

[#1]

Dear community,

since some time I am trying to solve a problem from the logistic sector with a dynamic dimension in a pivot table.

I would like to know how often (for example) "5 events at the same time" have been the "maximum number of events at the same time" between 9am to 10am.

Therefor I have created a calender with every minute from the start to the end date what allows me to count the number of events at the same minute. With this I am able to sort out the max. number of events at the same time by hour.

So far so good - but now I'd like to have table that counts the number of maximum events per hour which happen during a certain period.

Please find attached file with more information regarding this problem.

Maybe the solution is very simple or maybe impossible.

Anyway, I am thankful for every comment.

Cheers,

Nico

[#2]

Hmm, seems to me that no one has an idea yet?

Me neither.

[vidyut]

Hi,

The following Expression will give you max number of concurrent events occuring per minute in an hour:

Max(Aggr(Count(ID),MinutenkalenderDate,HourkalenderDate,Date))

If you keep the Date and Hour as Dimensions, this should give you the max concurrency for the hour.

[vidyut]

Here is the sample data generated:

HourkalenderDate	=Max(Aggr(Count(ID),MinutenkalenderDate,HourkalenderDate,Date))
	18
00:00 - 01:00	8
01:00 - 02:00	7
02:00 - 03:00	6
03:00 - 04:00	6
04:00 - 05:00	6
05:00 - 06:00	7
06:00 - 07:00	7
07:00 - 08:00	7
08:00 - 09:00	10
09:00 - 10:00	14
10:00 - 11:00	14
11:00 - 12:00	16
12:00 - 13:00	16
13:00 - 14:00	15
14:00 - 15:00	18
15:00 - 16:00	14
16:00 - 17:00	15
17:00 - 18:00	13
18:00 - 19:00	14
19:00 - 20:00	15
20:00 - 21:00	17
21:00 - 22:00	11
22:00 - 23:00	10
23:00 - 00:00	9

[#5]

Hi Vidyut,

thanks for your quick reply. Unfortunately this is not the solution I am looking for.

As mentioned before, I'd like to generate a table with the following dimensions - "HourkalenderDate" (static) and "Max. Number of Events" (dynamic) (see the file in the first contribution) - and the information should represent an aggregated value with respect to the following question:
How many times between - let's say - 14pm -15pm and during a given period (16 days) the "Max. Number of Events" was 1 or 2 or 3 or 4 or 5 or 6 or 7 or ... ?
Since there are just 16 values (one for each day) the sum of values must be 16 for every hour.

Example:

max. no. of events 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

"13:00 - 14:00" 2 4 2 2 1 2 1 0 1 0 0 1 0 0 0 0 0 0 0 = 16

"14:00 - 15:00" 1 3 2 2 0 0 1 0 1 0 2 0 1 2 0 0 0 0 1 = 16

Behind "1" for "14:00 - 15:00" and "8 events" stands a formula what considers that on just one of the 16 days the maximum number of events at the same time (by looking at the minutes), from 14pm- 15pm, was 8.

Maybe there is a work around?!