1 Reply Latest reply on Aug 12, 2012 3:28 PM by Joe Mako

    measure swapping and formatting

    fahim m

      Hi,

      I have used measure swapping as illustrated in

      http://kb.tableausoftware.com/articles/knowledgebase/parameterized-measure

       

      However, I have different measures

      one in hundreds

      one in thousands and

      one in millions.

       

      Can I control the formatting in the calculated field,

      so the measures show up in proper units. for example 6,000,0000 as 6m

                                                                                              600,000    as 6k

                                                                                               100           as 100

       

      My current calculated field to swap the measures is below

      CASE[Parameters].[Placeholder1]

      When "Number of Cruises" THEN [Number of Cruises] // Number of cruises is in hundreds//

      When "Cruise Nights" THEN [Cruise Nights]                    // Cruise Nights is in thousands//

      When "Passengers" THEN[Passengers]                          // Passengers in millions//

      When "Passengers Nights" THEN [Passengers Nights]  // Passengers Nights in millions//

      END

       

      I used the calculated field below as pointed out by Joe Mako - however it shows a syntax error. Please, Joe or anyone else help with this

       

      CASE[Parameters].[Placeholder1]

      When "Number of Cruises" THEN STR([Number of Cruises]) // Number of cruises is in hundreds//

      When "Cruise Nights" THEN STR(ROUND([Cruise Nights]/1000,1))+"K"                    // Cruise Nights is in thousands//

      When "Passengers" THEN STR(ROUND([Passengers]/1000000,1))+"M"                          // Passengers in millions//

      When "Passengers Nights" THEN STR(ROUND([Passengers Nights]/1000000,1))+"M"  // Passengers Nights in millions//

      END

       

      or to get thousands seperators as well,

       

      CASE[Parameters].[Placeholder1]

      When "Number of Cruises" THEN STR(LOOKUP([Number of Cruises],0)) // Number of cruises is in hundreds//

      When "Cruise Nights" THEN STR(LOOKUP(ROUND([Cruise Nights]/1000,1),0))+"K"                    // Cruise Nights is in thousands//

      When "Passengers" THEN STR(LOOKUP(ROUND([Passengers]/1000000,1),0))+"M"                          // Passengers in millions//

      When "Passengers Nights" THEN STR(LOOKUP(ROUND([Passengers Nights]/1000000,1),0))+"M"  // Passengers Nights in millions//

      END

        • 1. Re: measure swapping and formatting
          Joe Mako

          How about

           

          CASE[Parameters].[Placeholder1]

          When "Number of Cruises" THEN STR([Number of Cruises]) // Number of cruises is in hundreds//

          When "Cruise Nights" THEN STR(ROUND([Cruise Nights]/1000,1))+"K"                    // Cruise Nights is in thousands//

          When "Passengers" THEN STR(ROUND([Passengers]/1000000,1))+"M"                          // Passengers in millions//

          When "Passengers Nights" THEN STR(ROUND([Passengers Nights]/1000000,1))+"M"  // Passengers Nights in millions//

          END

           

          or to get thousands seperators as well,

           

          CASE[Parameters].[Placeholder1]

          When "Number of Cruises" THEN STR(LOOKUP([Number of Cruises],0)) // Number of cruises is in hundreds//

          When "Cruise Nights" THEN STR(LOOKUP(ROUND([Cruise Nights]/1000,1),0))+"K"                    // Cruise Nights is in thousands//

          When "Passengers" THEN STR(LOOKUP(ROUND([Passengers]/1000000,1),0))+"M"                          // Passengers in millions//

          When "Passengers Nights" THEN STR(LOOKUP(ROUND([Passengers Nights]/1000000,1),0))+"M"  // Passengers Nights in millions//

          END

          1 of 1 people found this helpful