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

    Aggregation to count max elements

    Dovile Momkute

      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

          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 Momkute

              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
                • Re: Aggregation to count max elements
                  Sunny Talwar

                  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 Momkute

                      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

                          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 Momkute

                              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