10 Replies Latest reply: Sep 28, 2017 8:04 AM by Nikos Tsachalis RSS

    Maximum value of sum per 4

    Nikos Tsachalis

      Hi community,

      You have helped me with this previous post :

      https://community.qlik.com/thread/262498?sr=stream

       

      In short., I have created a pivot table with dimensions 'Resource' and 'CollectionTime' (The granularity of the collected data is 15 minutes). The measure is 'Outbound Rate'. The goal was to find the busy hour (max of the above measure) per interface.

      So I used the suggested:

      Max(TOTAL Aggr(RangeSum(Above([Outbound Rate],0,4)), Resource, ([CollectionTime], (Ascending))))


      Now, I would like to find the max value of the 4 records that are used to calculate the above. For example. if the above maximum sum is calculated from A, B, C, D, I would like to get the max (A,B,C,D) 

      Will P() function get me anywhere?

      Thanks in advance.

        • Re: Maximum value of sum per 4
          Sunny Talwar

          Would you be able to share few rows of dummy data for this and the output you expect to see out of it?

            • Re: Maximum value of sum per 4
              Nikos Tsachalis
              RESOURCE_NAME NameSAMPLEDMAX_RATERangeSum(Above([MAX_RATE],0,4))(Max(TOTAL <RESOURCE_NAME> Aggr(RangeSum(Above([MAX_RATE],0,4)), RESOURCE_NAME, ([SAMPLED], (Ascending)))))
              A31-08-17 16:3036683135.42133676534.27220212595.04
              A31-08-17 16:4540661770.98141953999.94220212595.04
              A31-08-17 17:0038712481.36149055406.82220212595.04
              A31-08-17 17:1545842802.23161900189.99220212595.04
              A31-08-17 17:3052723194.53177940249.10220212595.04
              A31-08-17 17:4554438473.9191716952.02220212595.04
              A31-08-17 18:0051190219.33204194689.99220212595.04
              A31-08-17 18:1555967675.71214319563.47220212595.04
              A31-08-17 18:3058616226.1220212595.04220212595.04
              A31-08-17 18:4552310131.25218084252.39220212595.04
              A31-08-17 19:0044330279.31211224312.37220212595.04
              A31-08-17 19:1539591691.92194848328.58220212595.04
              A31-08-17 19:3041323221.94177555324.42220212595.04
              B
              B
              B

               

              I need to get the max of the the 4 values in bold in max_rate column for every resource

                • Re: Maximum value of sum per 4
                  Sunny Talwar

                  So you want to see 58616226.1 for RESOURCE_NAME A?

                    • Re: Maximum value of sum per 4
                      Nikos Tsachalis

                      I want to add a new column which will report the 58616226.1  for the RESOURCE_NAME  A, exactly

                        • Re: Maximum value of sum per 4
                          Sunny Talwar

                          May be this

                           

                          If(Max(TOTAL <RESOURCE_NAME> Aggr(RangeSum(Above([MAX_RATE], 0, 4)), RESOURCE_NAME, ([SAMPLED], (Ascending)))) = RangeSum(Above([MAX_RATE],0,4)), RangeMax(Above([MAX_RATE],0,4)))

                           

                          or this

                           

                          Max(TOTAL <RESOURCE_NAME> Aggr(

                           

                          If(Max(TOTAL <RESOURCE_NAME> Aggr(RangeSum(Above([MAX_RATE],0,4)), RESOURCE_NAME, ([SAMPLED], (Ascending)))) = RangeSum(Above([MAX_RATE],0,4)), RangeMax(Above([MAX_RATE],0,4)))


                          , RESOURCE_NAME, ([SAMPLED], (Ascending))))

                            • Re: Maximum value of sum per 4
                              Nikos Tsachalis

                              Actually this is not working exactly as I thought.

                              First suggestion returns nothing but the second one, it does return the maximum value but not daily.

                              So what I would like to do is to get the maximum value on daily basis. For this I have added one more dimension called 'date' so the new data looke like:

                               

                                     

                              RESOURCE_NAMEDateSAMPLEDMAX_RATESum4quartersMaxSumSuggestion
                              A9/25/201716:1534280083.39135286913.3157740934.2647660781.27
                              A9/25/201716:3036228893.19139676051.2157740934.2647660781.27
                              A9/25/201716:4538946111.42142850188.3157740934.2647660781.27
                              A9/25/201717:0036757460.91146212548.9157740934.2647660781.27
                              A9/25/201717:1540359446.39152291911.9157740934.2647660781.27
                              A9/25/201717:3040093152.46156156171.2157740934.2647660781.27
                              A9/25/201717:4536991696.38154201756.1157740934.2647660781.27
                              A9/25/201718:0040296639.03157740934.3157740934.2647660781.27
                              A9/26/201716:0038296381.65140018523.3176719505.6147660781.27
                              A9/26/201716:1531190186.52138933547.7176719505.6147660781.27
                              A9/26/201716:3035076780.37140049878.2176719505.6147660781.27
                              A9/26/201716:4541977729.53146541078.1176719505.6147660781.27
                              A9/26/201717:0042967028.95151211725.4176719505.6147660781.27
                              A9/26/201717:1541610230.8161631769.7176719505.6147660781.27
                              A9/26/201717:3044481464.59171036453.9176719505.6147660781.27
                              A9/26/201717:4547660781.27176719505.6176719505.6147660781.27

                               

                               

                              Dimensions: Resourse Name, Date, Sampled

                              Measure: MAX_RATE

                              Sum4quarters=RangeSum(Above([MAX_RATE],0,4))

                              MaxSum= (Max(TOTAL <RESOURCE_NAME,Date>Aggr(RangeSum(Above([MAX_RATE],0,4)),RESOURCE_NAME,(SAMPLED) )))

                              Suggestion = Max(TOTAL <RESOURCE_NAME> Aggr(

                              If(Max(TOTAL <RESOURCE_NAME> Aggr(RangeSum(Above([MAX_RATE],0,4)), RESOURCE_NAME, ([SAMPLED], (Ascending)))) = RangeSum(Above([MAX_RATE],0,4)), RangeMax(Above([MAX_RATE],0,4)))

                              , RESOURCE_NAME, ([SAMPLED], (Ascending))))

                               

                               

                              So for 9/25/2017 the suggestion column should have the value in green which is the max value of the 4 values that build the MaxSum. Right now it has the max of all per resource without daily separation. Any suggestion?