topic Re: Aggregation to count max elements in App Development

Aggregation to count max elements

Dovile_D — Sat, 25 Mar 2017 16:24:23 GMT

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.

Thank you in advance!

Re: Aggregation to count max elements

sunny_talwar — Sun, 26 Mar 2017 23:19:37 GMT

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

Dovile_D — Mon, 27 Mar 2017 04:13:33 GMT

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

sunny_talwar — Mon, 27 Mar 2017 08:39:29 GMT

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

LOAD time as TimeStamp,

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

Dovile_D — Mon, 27 Mar 2017 17:13:22 GMT

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

sunny_talwar — Mon, 27 Mar 2017 17:20:49 GMT

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

Dovile_D — Mon, 27 Mar 2017 18:09:55 GMT

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:

LOAD

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

sunny_talwar — Mon, 27 Mar 2017 18:55:43 GMT

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

Dovile_D — Mon, 27 Mar 2017 19:33:21 GMT

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