8 Replies Latest reply: Mar 27, 2017 3:33 PM by Dovile Momkute

# Aggregation to count max elements

Hello,

Maybe there's someone who could help with this operation:

Attributes used :

time (YYYY/MM/DD HH:MM) / LTE_cellname/ LTE_UL_Traffic

What I need is to find how many cells had the max traffic on the specific hour. The problem is that there is data for many days and the same cell on different day can have a different hour with max amount of traffic, so what I do, I use sum operation to identify which hour is the max traffic hour per all days.

What I tried:

Dimension - right(time,5)

Measure - count (DISTINCT {<sum(LTE_UL_Traffic)= {aggr(max(sum(LTE_UL_Traffic)), right(time,5))}>} LTE_cellname)

- that's how I imagine it, but it returns only error.

• ###### Re: Aggregation to count max elements

What exactly are you trying to get here? Count the max traffic hour for each day to determine which hour has the highest traffic over a certain period?

• ###### Re: Aggregation to count max elements

No, I need to know how many cells had the maximum traffic on each hour.

For example, data looks like that

 cell id 00:00 01:00 02:00 03:00 04:00 05:00 06:00 07:00 08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00 1 15 15 15 15 15 15 15 15 15 15 15 15 77 15 15 15 15 15 15 15 15 15 15 15 2 17 17 17 17 17 17 17 17 49 17 17 17 17 17 17 17 17 17 17 17 17 17 17 17 3 88 88 88 88 88 88 88 88 88 88 88 88 95 88 90 88 88 88 88 88 88 88 88 88

And the result should like this:

 00:00 01:00 02:00 03:00 04:00 05:00 06:00 07:00 08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00 count of cells 0 0 0 0 0 0 0 1 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0
• ###### Re: Aggregation to count max elements

First of all, create time field in the script and also create a key from time and

Time(Frac(time)) as Time,

LTE_cellname,

LTE_UL_Traffic,

AutoNumber(LTE_cellname&Time(Frac(time))) as Key

.....

Once this is done, you can try this

Count(DISTINCT {<Key = {"=Sum(LTE_UL_Traffic) = Max(TOTAL <LTE_cellname> Aggr(Sum(LTE_UL_Traffic), LTE_cellname, Time))"}>} LTE_cellname)

• ###### Re: Aggregation to count max elements

I did as you say and it doesn't seem to work. The syntax is correct, no errors but it returns no values.

Could you explain a little how this measure is constructed?

I understand set analysis as {<field = {value}>}, I am not very experienced with construction like having multiple '=' and what is the difference then value is between quotations? If there is some guide about it, I would appreciate it.

Also, our 'Key' is the number, does the expression between {} returns number?

• ###### Re: Aggregation to count max elements

Also, our 'Key' is the number, does the expression between {} returns number?

It doesn't, with {} is a search string which evaluates to true and false. It will only display those rows where it is evaluated to true.

Would you be able to provide a sample to take a look at?

• ###### Re: Aggregation to count max elements

I import csv files, but example could be like this:

 Date Time Cell Name UL Traffic Volume(MB) 01/02/2017 00:00 Oruva-Maz-L-U-2C781030 20.691 01/02/2017 00:00 Oruva-Maz-L-U-2C781032 24.5039 01/02/2017 00:00 Marcinkonys-L-R-2C653023 0 01/02/2017 01:00 Oruva-Maz-L-U-2C781030 28.9553 01/02/2017 01:00 Oruva-Maz-L-U-2C781032 12.1058 01/02/2017 01:00 Marcinkonys-L-R-2C653023 0 01/02/2017 02:00 Oruva-Maz-L-U-2C781030 27.4302 01/02/2017 02:00 Oruva-Maz-L-U-2C781032 11.2698 01/02/2017 02:00 Marcinkonys-L-R-2C653023 0 01/02/2017 03:00 Oruva-Maz-L-U-2C781032 0.7606 01/02/2017 03:00 Oruva-Maz-L-U-2C781030 11.0344 01/02/2017 03:00 Marcinkonys-L-R-2C653023 0 01/02/2017 04:00 Oruva-Maz-L-U-2C781032 1.1052 01/02/2017 04:00 Oruva-Maz-L-U-2C781030 3.8845 01/02/2017 04:00 Marcinkonys-L-R-2C653023 0.0001 01/02/2017 05:00 Oruva-Maz-L-U-2C781030 3.3021 01/02/2017 05:00 Oruva-Maz-L-U-2C781032 3.1747 01/02/2017 05:00 Marcinkonys-L-R-2C653023 0 01/02/2017 06:00 Oruva-Maz-L-U-2C781030 15.1937 01/02/2017 06:00 Oruva-Maz-L-U-2C781032 8.3273 01/02/2017 06:00 Marcinkonys-L-R-2C653023 0.0037 01/02/2017 07:00 Oruva-Maz-L-U-2C781030 22.2037 01/02/2017 07:00 Oruva-Maz-L-U-2C781032 25.3809 01/02/2017 07:00 Marcinkonys-L-R-2C653023 0.0237 01/02/2017 08:00 Oruva-Maz-L-U-2C781032 30.8563 01/02/2017 08:00 Oruva-Maz-L-U-2C781030 30.5751 01/02/2017 08:00 Marcinkonys-L-R-2C653023 0.0221 01/02/2017 09:00 Oruva-Maz-L-U-2C781032 47.8172 01/02/2017 09:00 Oruva-Maz-L-U-2C781030 118.7673 01/02/2017 09:00 Marcinkonys-L-R-2C653023 0.0861 01/02/2017 10:00 Oruva-Maz-L-U-2C781032 67.4189 01/02/2017 10:00 Oruva-Maz-L-U-2C781030 54.8285 01/02/2017 10:00 Marcinkonys-L-R-2C653023 0.0899 01/02/2017 11:00 Oruva-Maz-L-U-2C781030 47.2701 01/02/2017 11:00 Oruva-Maz-L-U-2C781032 67.6405 01/02/2017 11:00 Marcinkonys-L-R-2C653023 0.0258 01/02/2017 12:00 Oruva-Maz-L-U-2C781032 50.9347 01/02/2017 12:00 Oruva-Maz-L-U-2C781030 79.5431 01/02/2017 12:00 Marcinkonys-L-R-2C653023 0.0037 01/02/2017 13:00 Oruva-Maz-L-U-2C781032 40.9205 01/02/2017 13:00 Oruva-Maz-L-U-2C781030 43.7301 01/02/2017 13:00 Marcinkonys-L-R-2C653023 0.2752 01/02/2017 14:00 Oruva-Maz-L-U-2C781030 171.2237 01/02/2017 14:00 Oruva-Maz-L-U-2C781032 91.7315 01/02/2017 14:00 Marcinkonys-L-R-2C653023 0.0058 01/02/2017 15:00 Oruva-Maz-L-U-2C781030 140.0847 01/02/2017 15:00 Oruva-Maz-L-U-2C781032 63.033 01/02/2017 15:00 Marcinkonys-L-R-2C653023 0.0003 01/02/2017 16:00 Oruva-Maz-L-U-2C781032 76.2944 01/02/2017 16:00 Oruva-Maz-L-U-2C781030 106.6551 01/02/2017 16:00 Marcinkonys-L-R-2C653023 0 01/02/2017 17:00 Oruva-Maz-L-U-2C781032 68.8742 01/02/2017 17:00 Oruva-Maz-L-U-2C781030 308.9961 01/02/2017 17:00 Marcinkonys-L-R-2C653023 2.0753 01/02/2017 18:00 Oruva-Maz-L-U-2C781030 106.0531 01/02/2017 18:00 Oruva-Maz-L-U-2C781032 128.7013 01/02/2017 18:00 Marcinkonys-L-R-2C653023 0.009 01/02/2017 19:00 Oruva-Maz-L-U-2C781032 92.8656 01/02/2017 19:00 Oruva-Maz-L-U-2C781030 326.5558 01/02/2017 19:00 Marcinkonys-L-R-2C653023 0.017 01/02/2017 20:00 Oruva-Maz-L-U-2C781030 115.1571 01/02/2017 20:00 Oruva-Maz-L-U-2C781032 61.1563 01/02/2017 20:00 Marcinkonys-L-R-2C653023 0.105 01/02/2017 21:00 Oruva-Maz-L-U-2C781032 73.2544 01/02/2017 21:00 Oruva-Maz-L-U-2C781030 315.1157 01/02/2017 21:00 Marcinkonys-L-R-2C653023 0 01/02/2017 22:00 Oruva-Maz-L-U-2C781032 71.4112 01/02/2017 22:00 Oruva-Maz-L-U-2C781030 151.8991 01/02/2017 22:00 Marcinkonys-L-R-2C653023 0 01/02/2017 23:00 Oruva-Maz-L-U-2C781030 64.594 01/02/2017 23:00 Oruva-Maz-L-U-2C781032 34.2317 01/02/2017 23:00 Marcinkonys-L-R-2C653023 0 02/02/2017 00:00 Oruva-Maz-L-U-2C781032 21.9159 02/02/2017 00:00 Oruva-Maz-L-U-2C781030 169.1744 02/02/2017 00:00 Marcinkonys-L-R-2C653023 0 02/02/2017 01:00 Oruva-Maz-L-U-2C781030 242.3404 02/02/2017 01:00 Oruva-Maz-L-U-2C781032 2.9664 02/02/2017 01:00 Marcinkonys-L-R-2C653023 0 02/02/2017 02:00 Oruva-Maz-L-U-2C781030 90.8201 02/02/2017 02:00 Oruva-Maz-L-U-2C781032 1.3227 02/02/2017 02:00 Marcinkonys-L-R-2C653023 0 02/02/2017 03:00 Oruva-Maz-L-U-2C781032 5.7976 02/02/2017 03:00 Oruva-Maz-L-U-2C781030 51.1183 02/02/2017 03:00 Marcinkonys-L-R-2C653023 0.0002 02/02/2017 04:00 Oruva-Maz-L-U-2C781030 3.4914 02/02/2017 04:00 Oruva-Maz-L-U-2C781032 8.9662 02/02/2017 04:00 Marcinkonys-L-R-2C653023 0 02/02/2017 05:00 Oruva-Maz-L-U-2C781030 3.3456 02/02/2017 05:00 Oruva-Maz-L-U-2C781032 4.9403 02/02/2017 05:00 Marcinkonys-L-R-2C653023 0 02/02/2017 06:00 Oruva-Maz-L-U-2C781030 17.2746 02/02/2017 06:00 Oruva-Maz-L-U-2C781032 30.3978 02/02/2017 06:00 Marcinkonys-L-R-2C653023 0.0002 02/02/2017 07:00 Oruva-Maz-L-U-2C781030 26.998 02/02/2017 07:00 Oruva-Maz-L-U-2C781032 43.8524 02/02/2017 07:00 Marcinkonys-L-R-2C653023 0.1446 02/02/2017 08:00 Oruva-Maz-L-U-2C781032 68.1312 02/02/2017 08:00 Oruva-Maz-L-U-2C781030 80.03 02/02/2017 08:00 Marcinkonys-L-R-2C653023 0.0461 02/02/2017 09:00 Oruva-Maz-L-U-2C781032 62.2785 02/02/2017 09:00 Oruva-Maz-L-U-2C781030 264.067 02/02/2017 09:00 Marcinkonys-L-R-2C653023 0.0232 02/02/2017 10:00 Oruva-Maz-L-U-2C781032 87.8691 02/02/2017 10:00 Oruva-Maz-L-U-2C781030 132.6563 02/02/2017 10:00 Marcinkonys-L-R-2C653023 0.0506 02/02/2017 11:00 Oruva-Maz-L-U-2C781030 76.4323 02/02/2017 11:00 Oruva-Maz-L-U-2C781032 81.2556 02/02/2017 11:00 Marcinkonys-L-R-2C653023 0.0127 02/02/2017 12:00 Oruva-Maz-L-U-2C781032 85.3078 02/02/2017 12:00 Oruva-Maz-L-U-2C781030 64.2731 02/02/2017 12:00 Marcinkonys-L-R-2C653023 0.5098 02/02/2017 13:00 Oruva-Maz-L-U-2C781032 47.2426 02/02/2017 13:00 Oruva-Maz-L-U-2C781030 122.0355 02/02/2017 13:00 Marcinkonys-L-R-2C653023 0.049 02/02/2017 14:00 Oruva-Maz-L-U-2C781030 111.1105 02/02/2017 14:00 Oruva-Maz-L-U-2C781032 163.6791 02/02/2017 14:00 Marcinkonys-L-R-2C653023 0.0317 02/02/2017 15:00 Oruva-Maz-L-U-2C781030 84.5726 02/02/2017 15:00 Oruva-Maz-L-U-2C781032 175.1026 02/02/2017 15:00 Marcinkonys-L-R-2C653023 0.1194 02/02/2017 16:00 Oruva-Maz-L-U-2C781030 107.2077 02/02/2017 16:00 Oruva-Maz-L-U-2C781032 75.2978 02/02/2017 16:00 Marcinkonys-L-R-2C653023 0.1182 02/02/2017 17:00 Oruva-Maz-L-U-2C781032 69.0403 02/02/2017 17:00 Oruva-Maz-L-U-2C781030 412.4494 02/02/2017 17:00 Marcinkonys-L-R-2C653023 0.0288 02/02/2017 18:00 Oruva-Maz-L-U-2C781030 152.3702 02/02/2017 18:00 Oruva-Maz-L-U-2C781032 64.0453 02/02/2017 18:00 Marcinkonys-L-R-2C653023 0.0468 02/02/2017 19:00 Oruva-Maz-L-U-2C781030 524.0624 02/02/2017 19:00 Oruva-Maz-L-U-2C781032 71.9918 02/02/2017 19:00 Marcinkonys-L-R-2C653023 0.0029 02/02/2017 20:00 Oruva-Maz-L-U-2C781032 55.391 02/02/2017 20:00 Oruva-Maz-L-U-2C781030 92.0433 02/02/2017 20:00 Marcinkonys-L-R-2C653023 0.0517 02/02/2017 21:00 Oruva-Maz-L-U-2C781032 52.6384 02/02/2017 21:00 Oruva-Maz-L-U-2C781030 239.5099 02/02/2017 21:00 Marcinkonys-L-R-2C653023 0.0115 02/02/2017 22:00 Oruva-Maz-L-U-2C781032 47.0186 02/02/2017 22:00 Oruva-Maz-L-U-2C781030 213.1274 02/02/2017 22:00 Marcinkonys-L-R-2C653023 0.0038 02/02/2017 23:00 Oruva-Maz-L-U-2C781030 48.0594 02/02/2017 23:00 Oruva-Maz-L-U-2C781032 61.6194 02/02/2017 23:00 Marcinkonys-L-R-2C653023 0

And my source changed a little regarding date/time compared to my first post and still I applied your suggestions and data load script looks like that:

LTE:

date#("Date"&' '&"Time", 'DD/MM/YYYY hh:mm') as timestamp,

Time("Time",'hh:mm') as Time,

AutoNumber(  "Cell Name" & Time("Time",'hh:mm')) as Key,

"Cell Name" as LTE_cellname,

"UL Traffic Volume(MB)" as LTE_UL_Traffic

• ###### Re: Aggregation to count max elements

Try this:

=Sum(Aggr(If(Sum(LTE_UL_Traffic) = Max(TOTAL <[Cell Name]> Aggr(Sum(LTE_UL_Traffic), [Cell Name], Time)), 1, 0), [Cell Name], Time))

• ###### Re: Aggregation to count max elements

Thank you very much! It works as I wanted And the solution itself is just simply fascinating!