4 Replies Latest reply: Aug 16, 2013 10:13 AM by Kevin McGovern RSS

    Related Maximum Value in Calculated Dimension

    Kevin McGovern

      I'm trying to add a calculated dimension to a pivot table I built a few months back.  The dimension needs to show the last approver in the sequence (or approvers if there is more than one approver with the same order number) while also being sensitive to selection.  I've tried a number of different aggregations and explored using set analysis but have been unsuccessful for the most part. I've attached a sample app that includes my latest dimesnion but this still isn't returning the correct results.  I've also included some samples of expected outputs based on selections in the below table.

       

      Approvers Selected
      Names Displayed in Dim
      None5- Todd    20- Bill,Tony
      Bill, Joe, John5- Bill    20- Bill
      Harry, John, Todd, Tony5- Todd    20- Tony
          • Re: Related Maximum Value in Calculated Dimension
            Kevin McGovern

            This is perfect! Thank you so much.  Would you mind giving some background on what your thought process was behind the expression?  It would be really helpful to understand for my own purposes (as well as others on the forum).  Thank you again!

              • Re: Related Maximum Value in Calculated Dimension
                Stefan Wühl

                If you want to create a dimension you can either use a plain field or a calculcated dimension based on a field, e.g.

                 

                =FIELD

                 

                =if(FIELD > 10, FIELD)

                 

                For more complex tasks, you can use advanced aggregation, which essentially produces again a one dimensional vector as dimension, one value per combination of advanced aggregation dimension values.

                 

                [Another, different option to create a dimension is a synthetic dimension using Valuelist() / Valueloop]

                 

                You want to implement something more complex than just a (filtered) FIELD, so let's start with advanced aggregation:

                 

                aggr( "EXP", "DIMs")

                 

                Your DIMs should be ID, since you want a value per ID.

                 

                aggr( "EXP", ID)

                 

                At this point, I sometimes create a straight table chart with dimension ID (in this case) and play around with the expression until I get what I want. We want "last approver in the sequence", and if I read something like this, I go for the FirstSortedValue() function.

                 

                Sequence mean sorted by Order per ID, and approver equals Name, so maybe something like

                =FirstSortedValue( Name, -Order)

                 

                This will not handle the multiple approvers with same order number correctly, so we need to add an aggregation (and an aggregation within an aggregation like FirstSortedValue requires advanced aggregation again:

                 

                =FirstSortedValue( aggr(concat(Name,', '), ID, Order), -aggr( Order, ID, Order) )

                 

                and putting all together in one expression for the calculated dimension:

                =Aggr(FirstSortedValue( aggr(concat(Name,', '), ID, Order), -aggr( Order, ID, Order)),ID )

                 

                Regards,

                Stefan