14 Replies Latest reply on Dec 10, 2017 6:08 AM by Matthew Fahy

    Calculation to return the maximum value for each user?

    Mike Greening

      I've got data that looks like this:

       

      User     Value

      A          1

      A          2

      A          3

      A          4

      B          1

      B          2

      C          1

      C          2

      D          1

      E          1

      E          2

      F          1

      F          2

      F          3

      F          4

       

      How can I create a calculation that returns only the maximum level achieved for each user?  Creating a MAX(value) pill generates a single data point, in this case 4, which is not what I'm looking for.  Creating a discrete (value) pill as a dimension includes every value for every user, also not what I'm after. I'd like to use the calculation to create a bar chart (distinct count of users vs. maximum value) where each bar is a count of users at their maximum value.  In the example above, the bar at value "1" would have 1 user, value "2" would have 3 users, value "3" would have 0 users, and value "4" would have 2 users.  Thanks!

        • 1. Re: Calculation to return the maximum value for each user?
          Joe Mako

          There are quite a few routes to get the result you are asking for here, an aggregation of an aggregation. This can be done with stacked bar marks and a reference line, or a WINDOW_SUM() table calc.

           

          Attached are examples two. If your situation is more complex or you have other requirements or constraints, another route may be a better fit.

           

          Notice on the table calc worksheet that the pill MAX(Value) is set to be ignored in table calculations, this causes it to be used for partitioning, and the compute using (addressing) of the table calc pill is set to User. To get the label at the end of the bar, stacked marks was turned off.

           

          The table calc route would enable derivative calculations or other display options not available with the simple stacked bar marks.

          3 of 3 people found this helpful
          • 2. Re: Calculation to return the maximum value for each user?
            Mike Greening

            Thanks Joe, this appears to have worked!  I'm not sure why yet, but I'll figure it out   Thanks for your help!

            • 3. Re: Calculation to return the maximum value for each user?
              Paul Hare

              Joe:

              I have a variation on the above problem:

               

              Requirement       User          Score

              1                        A                    1

              1                        B                    2

              1                        C                    0

              2                        A                    2

              2                        B                    3

              2                        C                    3

              3                        A                    2

              3                        B                    0

              3                        C                    0

               

              Want to be able to display the Maximum score by requirement and the related user(s), such that:

               

              1          B          2

              2          B          3

              2          C          3

              3          A          2

               

              Make sense?

              • 4. Re: Calculation to return the maximum value for each user?
                Joe Mako

                how about something like the attached?

                1 of 1 people found this helpful
                • 5. Re: Calculation to return the maximum value for each user?
                  Leonardo Faoro

                  Hi Joe

                  I just found this topic today, and it helped me resolve an issue that I had been dealing with for a long time. Unfortunately I don't understand how the calculation below works. Would you please be able to explain? Or point to a place where I could learn these functions?

                   

                  IF FIRST()==0 THEN

                  WINDOW_SUM(MAX(1),0,IIF(FIRST()==0,LAST(),0))

                  END

                   

                  many thanks
                  Leo

                  • 6. Re: Calculation to return the maximum value for each user?
                    Aaron Clancy

                    Hi Leonardo,

                    That calculation has some redundant logic in it which could probably cause it to be a little confusing.

                    First off, It is the equivalent of         if index() = 1 then size() end

                     

                    The calculation will only be evaluated for the first position so any reference to a comparator of first()==0 will always evelate to true in any subsequent logic.

                     

                    The parameters taken in for window_sum are:

                    window_sum(measure, starting position relative to location, ending position relative to location)

                    If the position is a number then it's the starting position relative to the location in the partition.

                    If it is first() or last() then it is the first or last position in the partition.

                     

                    In this example the starting position is the first one or "first() = 0" so your starting point to sum is the first value in the partition.

                    The ending position is a calculation:

                    IIF(FIRST()==0,LAST(),0)

                     

                    This is an if then statement.  If true then the ending position is last() else ending position is  0 positions away from current location.

                     

                    first() in this instance can never evaluate to anything other than 0 because you never leave the first location in the partition.

                    so the ending point is always the last record in the partition.

                    This amounts to "How many values do I have in my partition?"  This can be achieve with the size() function.

                     

                     

                    Simpler calc:

                     

                    if index() = 1 then size() end

                     

                    Logic:  If I am the first record in the partition output the number of values in the partition.

                     

                     

                    Let me know if that helps your understanding of table calcs.

                    • 7. Re: Calculation to return the maximum value for each user?
                      Leonardo Faoro

                      Hi Aaron, thank you very much for the thorough explanation. I think I get it. In my use case, what I want is not necessarily the first item in the partition, I want the item with the maximum value for that field. So does sorting by this field influence what this calculation gets?

                       

                      thanks
                      Leo

                      • 8. Re: Calculation to return the maximum value for each user?
                        Aaron Clancy

                        Hi Leonard,

                         

                        I was just explaining the calc in question, it might not relate at all to your scenario.

                         

                        If you want the max mark to show and restrict the rest there are a few ways to do this.

                         

                        If your data is hierarchical meaning that your lower level value (color) exist in only one instance of the higher level then sorting is fairly straight forward and you can sort your color dimension.

                         

                        If it's not then you'll need to filter to the max value of the lower level with respect to the higher level.

                        Here's an example I created that shows the difference, outlining the two scenarios and the common mistake that people make when they try to sort a non-heirarchical relationship.

                        Screen Shot 2014-11-07 at 11.41.02 AM.png

                        • 9. Re: Calculation to return the maximum value for each user?
                          Leonardo Faoro

                          thank you Aaron. Clearly I have a lot to learn on this topic still

                           

                          Leo

                          • 10. Re: Calculation to return the maximum value for each user?
                            Manish Baid

                            Hi,

                             

                            I have another case where i need max for a particular level -

                             

                            Say for example -

                             

                            A 1

                            A 2

                            A 3

                            B 1

                            B 5

                            C 2

                            C 7

                             

                            Desired output -

                             

                            A 1 3

                            A 2 3

                            A 3 3

                            B 1 5

                            B 5 5

                            C 2 7

                            C 7 7

                             

                            Here 3 is for the values of set A, 5 is the max for the values of set B and so on.

                             

                            Thanks in advance.

                            1 of 1 people found this helpful
                            • 12. Re: Calculation to return the maximum value for each user?
                              David Hines

                              {FIXED [Letter Column] : MAX([Number Column]}

                              1 of 1 people found this helpful
                              • 13. Re: Calculation to return the maximum value for each user?
                                ed.d.1

                                I just wonder if you have ever discussed it here. I am just looking for a function to create a calculated field which could exclude  two highest and two lowest values in a given set. thank you in advanced!

                                • 14. Re: Calculation to return the maximum value for each user?
                                  Matthew Fahy

                                  Use the FIXED Function:

                                   

                                  {FIXED [Criteria 1],[Criteria 2],... : [Calculation]}

                                   

                                  in your example the solution would be:

                                   

                                  {FIXED [User] : MAX([Value])}

                                   

                                  in addition if you would like it to be a table calculation on the value, you would need to use the FIXED function as well, but without any criteria (columns) listed:

                                   

                                  {FIXED : MAX([Value])}