Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik GA: Multivariate Time Series in Qlik Predict: Get Details
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Dovile_D
Contributor II
Contributor II
‎2017-03-25 12:24 PM

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.

Thank you in advance!

2,828 Views
0 Likes
1 Solution

Accepted Solutions
sunny_talwar
MVP
MVP
‎2017-03-27 02:55 PM

In response to Dovile_D

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))


Capture.PNG

View solution in original post

254730.qvw
2,738 Views
0 Likes
8 Replies
sunny_talwar
MVP
MVP
‎2017-03-26 07:19 PM

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?

2,738 Views
0 Likes
Dovile_D
Contributor II
Contributor II
‎2017-03-27 12:13 AM
Author

In response to sunny_talwar

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

For example, data looks like that

   

cell id00:0001:0002:0003:0004:0005:0006:0007:0008:0009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00
1151515151515151515151515771515151515151515151515
2171717171717171749171717171717171717171717171717
3888888888888888888888888958890888888888888888888

And the result should like this:

   

00:0001:0002:0003:0004:0005:0006:0007:0008:0009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00
count of cells000 00001000200000000000
2,738 Views
0 Likes
sunny_talwar
MVP
MVP
‎2017-03-27 04:39 AM

In response to Dovile_D

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)

2,738 Views
Dovile_D
Contributor II
Contributor II
‎2017-03-27 01:13 PM
Author

In response to sunny_talwar

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?

2,738 Views
0 Likes
sunny_talwar
MVP
MVP
‎2017-03-27 01:20 PM

In response to Dovile_D

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?

2,738 Views
Dovile_D
Contributor II
Contributor II
‎2017-03-27 02:09 PM
Author

In response to sunny_talwar

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

   

DateTimeCell NameUL Traffic Volume(MB)
01/02/201700:00Oruva-Maz-L-U-2C78103020.691
01/02/201700:00Oruva-Maz-L-U-2C78103224.5039
01/02/201700:00Marcinkonys-L-R-2C6530230
01/02/201701:00Oruva-Maz-L-U-2C78103028.9553
01/02/201701:00Oruva-Maz-L-U-2C78103212.1058
01/02/201701:00Marcinkonys-L-R-2C6530230
01/02/201702:00Oruva-Maz-L-U-2C78103027.4302
01/02/201702:00Oruva-Maz-L-U-2C78103211.2698
01/02/201702:00Marcinkonys-L-R-2C6530230
01/02/201703:00Oruva-Maz-L-U-2C7810320.7606
01/02/201703:00Oruva-Maz-L-U-2C78103011.0344
01/02/201703:00Marcinkonys-L-R-2C6530230
01/02/201704:00Oruva-Maz-L-U-2C7810321.1052
01/02/201704:00Oruva-Maz-L-U-2C7810303.8845
01/02/201704:00Marcinkonys-L-R-2C6530230.0001
01/02/201705:00Oruva-Maz-L-U-2C7810303.3021
01/02/201705:00Oruva-Maz-L-U-2C7810323.1747
01/02/201705:00Marcinkonys-L-R-2C6530230
01/02/201706:00Oruva-Maz-L-U-2C78103015.1937
01/02/201706:00Oruva-Maz-L-U-2C7810328.3273
01/02/201706:00Marcinkonys-L-R-2C6530230.0037
01/02/201707:00Oruva-Maz-L-U-2C78103022.2037
01/02/201707:00Oruva-Maz-L-U-2C78103225.3809
01/02/201707:00Marcinkonys-L-R-2C6530230.0237
01/02/201708:00Oruva-Maz-L-U-2C78103230.8563
01/02/201708:00Oruva-Maz-L-U-2C78103030.5751
01/02/201708:00Marcinkonys-L-R-2C6530230.0221
01/02/201709:00Oruva-Maz-L-U-2C78103247.8172
01/02/201709:00Oruva-Maz-L-U-2C781030118.7673
01/02/201709:00Marcinkonys-L-R-2C6530230.0861
01/02/201710:00Oruva-Maz-L-U-2C78103267.4189
01/02/201710:00Oruva-Maz-L-U-2C78103054.8285
01/02/201710:00Marcinkonys-L-R-2C6530230.0899
01/02/201711:00Oruva-Maz-L-U-2C78103047.2701
01/02/201711:00Oruva-Maz-L-U-2C78103267.6405
01/02/201711:00Marcinkonys-L-R-2C6530230.0258
01/02/201712:00Oruva-Maz-L-U-2C78103250.9347
01/02/201712:00Oruva-Maz-L-U-2C78103079.5431
01/02/201712:00Marcinkonys-L-R-2C6530230.0037
01/02/201713:00Oruva-Maz-L-U-2C78103240.9205
01/02/201713:00Oruva-Maz-L-U-2C78103043.7301
01/02/201713:00Marcinkonys-L-R-2C6530230.2752
01/02/201714:00Oruva-Maz-L-U-2C781030171.2237
01/02/201714:00Oruva-Maz-L-U-2C78103291.7315
01/02/201714:00Marcinkonys-L-R-2C6530230.0058
01/02/201715:00Oruva-Maz-L-U-2C781030140.0847
01/02/201715:00Oruva-Maz-L-U-2C78103263.033
01/02/201715:00Marcinkonys-L-R-2C6530230.0003
01/02/201716:00Oruva-Maz-L-U-2C78103276.2944
01/02/201716:00Oruva-Maz-L-U-2C781030106.6551
01/02/201716:00Marcinkonys-L-R-2C6530230
01/02/201717:00Oruva-Maz-L-U-2C78103268.8742
01/02/201717:00Oruva-Maz-L-U-2C781030308.9961
01/02/201717:00Marcinkonys-L-R-2C6530232.0753
01/02/201718:00Oruva-Maz-L-U-2C781030106.0531
01/02/201718:00Oruva-Maz-L-U-2C781032128.7013
01/02/201718:00Marcinkonys-L-R-2C6530230.009
01/02/201719:00Oruva-Maz-L-U-2C78103292.8656
01/02/201719:00Oruva-Maz-L-U-2C781030326.5558
01/02/201719:00Marcinkonys-L-R-2C6530230.017
01/02/201720:00Oruva-Maz-L-U-2C781030115.1571
01/02/201720:00Oruva-Maz-L-U-2C78103261.1563
01/02/201720:00Marcinkonys-L-R-2C6530230.105
01/02/201721:00Oruva-Maz-L-U-2C78103273.2544
01/02/201721:00Oruva-Maz-L-U-2C781030315.1157
01/02/201721:00Marcinkonys-L-R-2C6530230
01/02/201722:00Oruva-Maz-L-U-2C78103271.4112
01/02/201722:00Oruva-Maz-L-U-2C781030151.8991
01/02/201722:00Marcinkonys-L-R-2C6530230
01/02/201723:00Oruva-Maz-L-U-2C78103064.594
01/02/201723:00Oruva-Maz-L-U-2C78103234.2317
01/02/201723:00Marcinkonys-L-R-2C6530230
02/02/201700:00Oruva-Maz-L-U-2C78103221.9159
02/02/201700:00Oruva-Maz-L-U-2C781030169.1744
02/02/201700:00Marcinkonys-L-R-2C6530230
02/02/201701:00Oruva-Maz-L-U-2C781030242.3404
02/02/201701:00Oruva-Maz-L-U-2C7810322.9664
02/02/201701:00Marcinkonys-L-R-2C6530230
02/02/201702:00Oruva-Maz-L-U-2C78103090.8201
02/02/201702:00Oruva-Maz-L-U-2C7810321.3227
02/02/201702:00Marcinkonys-L-R-2C6530230
02/02/201703:00Oruva-Maz-L-U-2C7810325.7976
02/02/201703:00Oruva-Maz-L-U-2C78103051.1183
02/02/201703:00Marcinkonys-L-R-2C6530230.0002
02/02/201704:00Oruva-Maz-L-U-2C7810303.4914
02/02/201704:00Oruva-Maz-L-U-2C7810328.9662
02/02/201704:00Marcinkonys-L-R-2C6530230
02/02/201705:00Oruva-Maz-L-U-2C7810303.3456
02/02/201705:00Oruva-Maz-L-U-2C7810324.9403
02/02/201705:00Marcinkonys-L-R-2C6530230
02/02/201706:00Oruva-Maz-L-U-2C78103017.2746
02/02/201706:00Oruva-Maz-L-U-2C78103230.3978
02/02/201706:00Marcinkonys-L-R-2C6530230.0002
02/02/201707:00Oruva-Maz-L-U-2C78103026.998
02/02/201707:00Oruva-Maz-L-U-2C78103243.8524
02/02/201707:00Marcinkonys-L-R-2C6530230.1446
02/02/201708:00Oruva-Maz-L-U-2C78103268.1312
02/02/201708:00Oruva-Maz-L-U-2C78103080.03
02/02/201708:00Marcinkonys-L-R-2C6530230.0461
02/02/201709:00Oruva-Maz-L-U-2C78103262.2785
02/02/201709:00Oruva-Maz-L-U-2C781030264.067
02/02/201709:00Marcinkonys-L-R-2C6530230.0232
02/02/201710:00Oruva-Maz-L-U-2C78103287.8691
02/02/201710:00Oruva-Maz-L-U-2C781030132.6563
02/02/201710:00Marcinkonys-L-R-2C6530230.0506
02/02/201711:00Oruva-Maz-L-U-2C78103076.4323
02/02/201711:00Oruva-Maz-L-U-2C78103281.2556
02/02/201711:00Marcinkonys-L-R-2C6530230.0127
02/02/201712:00Oruva-Maz-L-U-2C78103285.3078
02/02/201712:00Oruva-Maz-L-U-2C78103064.2731
02/02/201712:00Marcinkonys-L-R-2C6530230.5098
02/02/201713:00Oruva-Maz-L-U-2C78103247.2426
02/02/201713:00Oruva-Maz-L-U-2C781030122.0355
02/02/201713:00Marcinkonys-L-R-2C6530230.049
02/02/201714:00Oruva-Maz-L-U-2C781030111.1105
02/02/201714:00Oruva-Maz-L-U-2C781032163.6791
02/02/201714:00Marcinkonys-L-R-2C6530230.0317
02/02/201715:00Oruva-Maz-L-U-2C78103084.5726
02/02/201715:00Oruva-Maz-L-U-2C781032175.1026
02/02/201715:00Marcinkonys-L-R-2C6530230.1194
02/02/201716:00Oruva-Maz-L-U-2C781030107.2077
02/02/201716:00Oruva-Maz-L-U-2C78103275.2978
02/02/201716:00Marcinkonys-L-R-2C6530230.1182
02/02/201717:00Oruva-Maz-L-U-2C78103269.0403
02/02/201717:00Oruva-Maz-L-U-2C781030412.4494
02/02/201717:00Marcinkonys-L-R-2C6530230.0288
02/02/201718:00Oruva-Maz-L-U-2C781030152.3702
02/02/201718:00Oruva-Maz-L-U-2C78103264.0453
02/02/201718:00Marcinkonys-L-R-2C6530230.0468
02/02/201719:00Oruva-Maz-L-U-2C781030524.0624
02/02/201719:00Oruva-Maz-L-U-2C78103271.9918
02/02/201719:00Marcinkonys-L-R-2C6530230.0029
02/02/201720:00Oruva-Maz-L-U-2C78103255.391
02/02/201720:00Oruva-Maz-L-U-2C78103092.0433
02/02/201720:00Marcinkonys-L-R-2C6530230.0517
02/02/201721:00Oruva-Maz-L-U-2C78103252.6384
02/02/201721:00Oruva-Maz-L-U-2C781030239.5099
02/02/201721:00Marcinkonys-L-R-2C6530230.0115
02/02/201722:00Oruva-Maz-L-U-2C78103247.0186
02/02/201722:00Oruva-Maz-L-U-2C781030213.1274
02/02/201722:00Marcinkonys-L-R-2C6530230.0038
02/02/201723:00Oruva-Maz-L-U-2C78103048.0594
02/02/201723:00Oruva-Maz-L-U-2C78103261.6194
02/02/201723:00Marcinkonys-L-R-2C6530230

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

2,738 Views
0 Likes
sunny_talwar
MVP
MVP
‎2017-03-27 02:55 PM

In response to Dovile_D

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))


Capture.PNG

254730.qvw
2,739 Views
0 Likes
Dovile_D
Contributor II
Contributor II
‎2017-03-27 03:33 PM
Author

In response to sunny_talwar

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

2,738 Views
0 Likes