Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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))
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?
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 |
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)
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?
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?
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
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))
Thank you very much! It works as I wanted And the solution itself is just simply fascinating!