7 Replies Latest reply on May 26, 2020 5:45 AM by Curtis Lam

    Creating new field to rank measures by using measure names

    Curtis Lam

      Good Morning All,

       

      Hope everyone is keeping safe and well.

       

      Understand that my request may seem a bit strange, but hoping you can help me with a solution.

       

      ReferenceBlackGreen
      BlueRedWhiteOutput
      Customer 11

      Green

      Customer 20.50.10.20.2Black, Red, White, Green
      Customer 30.50.5Blue, Red

       

      I am trying to create an output using Measure Names which ranks based on highest to lowest Measure Values.

       

      If the values are the same, it doesn't matter which order it is in - i.e. for customer 3, this could be Red, Blue or Blue, Red.

       

      Thanks

        • 1. Re: Creating new field to rank measures by using measure names
          Steve Wood

          Hi Curtis,

           

          If you can pivot your data you can do something like this:

           

           

          I've just dragged colour onto text and sorted by a field - value descending - then added a comma into "Text" label. NULL values filtered out.

           

          I did the pivot in the data window:

           

          This is okay if you can pivot, don't mind the extra comma on the end and were happy for the result to be on the view and not a calculated field to be used elsewhere.

           

          How well does that suit your needs? Post back and someone will help out more if need be!

           

          Ta,

          Steve

           

          PS. Workbook attached (v 2020.1)

           

          PPS. If you're after a calculation based method then you can use table calculations - TABLEAU GURUS: Combining Seperate Rows into Single Row List

          1 of 1 people found this helpful
          • 2. Re: Creating new field to rank measures by using measure names
            Curtis Lam

            This is great - thank you. I did not know you could pivot in Tableau, so that definitely solved a lot of problems.

             

            Just one question on the output order, how can I make it show in order of the largest value for each Row?

             

            For example:

            Your output for Customer 2 shows as: Green, Red, Black, White - 0.1, 0.2, 0.5, 0.2

            The output i would like: Black, Red, White, Green or Black, White, Red, Green - 0.5, 0.2, 0.2, 0.1

             

            Thank you,

            Curtis

            • 3. Re: Creating new field to rank measures by using measure names
              Curtis Lam

              Steve Wood

               

              Hi Steve, not sure if you've had the time to review my follow-up question above? Thank you

              Curtis

              • 4. Re: Creating new field to rank measures by using measure names
                Steve Wood

                Hi Curtis,

                 

                Ah no, for some reason I’m not getting all the usual email updates from forum responses so thanks for the @ mentio!

                 

                I actually thought I’d handled to sort order, but as you point out it doesn’t look right! You’ll see in my screenshot that there is a sort on the “colour” pill: I dragged colour onto text and then right clicked it and sorted by a field - picking “value“ and “descending”. But perhaps I messed this up before doing the screenshot. Did you give that a go?

                 

                Ta, Steve

                • 5. Re: Creating new field to rank measures by using measure names
                  Curtis Lam

                  Hi Steve Wood,

                   

                  No problem at all, thanks for your reply.

                   

                  Yes, I did try this with my real data however similar to your screenshot above I think this is sorting it in order of column total i.e. Green will always come first if it appears for that row because the total of that column is 2.

                   

                  Instead I need to be able to sort it order by largest to smallest for each row. I also tried several LOD calculations to attempt to force tableau to calculate this by each row and using that as the sort, but I still haven't found out what the problem is yet.

                   

                  Thanks,

                  Curtis

                  • 6. Re: Creating new field to rank measures by using measure names
                    Steve Wood

                    Hi Curtis,

                     

                    I see the problem. It's sorting by the overall total for each colour, which makes perfect sense now I stop and think about it. This sheet illustrates:

                     

                     

                    I have another option for you using table calculations rather than LOD expressions, as we can make use of the PREVIOUS_VALUE function to build up a comma separated list. I have a feeling I've over complicated this a little but here's the rough steps.

                     

                    First up I've created a calculation called [i] which is just = INDEX(). I've dragged that on to columns, then pulled colour and value onto the values/text. When you do this is won't initially look like the below...

                     

                     

                    I had to right click the [i] pill on columns and selected edit table calculation. Then I was able to compute using specific dimensions; reorder reference and colour so that I could restart every reference (so each customer get's their own unique index/ranking essentially); finally I had to set a custom sort order on Value - SUM - descending.

                     

                     

                    This should now mean that for each customer the colours are sorted into their positions [i] based on descending order of value.

                     

                    From here we can switch the text shown over to a calculation using PREVIOUS_VALUE...

                     

                    I created a calculation [Colour list] which =

                     

                    PREVIOUS_VALUE('')

                    +

                    IF NOT ISNULL(SUM([Value]))

                    THEN IF PREVIOUS_VALUE('') != '' THEN ', ' ELSE '' END + MIN([Colour])

                    ELSE ''

                    END

                     

                    PREVIOUS_VALUE uses the previous value of the whole calculation in the partition, or the value passed in to start with. So above you see that we concatenate each colour onto the PREVIOUS_VALUE if that colour has a non-NULL value. There is also some messing about to ensure that we only get a comma between values.

                     

                    I dragged this onto Text in place of Colour/Value, but retained Colour on detail. I then had to edit the table calculation (like I did for [i] above) to calculate/restart in the right order.

                     

                     

                    In the above we get to see Tableau's workings as we read across - so we see Black in spot one, then Black, Red and so on. Obviously we really only want the final iteration to show. So the last step is to add a filter on another table calculation to just show the last [i] column - again this needs editing to calculate/restart in the right order.

                     

                    [Final list?]: LAST()=0

                    Filtered to TRUE

                     

                     

                    Seems a bit long winded eh! An alternative is to fix this up in some data prep steps I guess?

                     

                    Ta,

                    Steve.

                     

                    PS. Workbook attached.

                    • 7. Re: Creating new field to rank measures by using measure names
                      Curtis Lam

                      Thanks Steve - this solution worked perfectly for me.

                       

                      Best regards,

                      Curtis