1 Reply Latest reply on May 22, 2017 12:44 AM by Norbert Maijoor

    Tableau Hit Ratio by Country by State

    Hemalatha Dave

      Hi, I am trying to create a calculated field that will calculate the hit ratio by country, by state. Example is below (need the new field)

       

      I want to do this as calculated field. This new field will be used to multiply with total sales for the company.

       

      Logic is :

       

      All Stage 3 are Stage 2 and Stage 1

      All Stage 2 is Stage 1

       

      When Status=Stage1 then [Count(Stage3) with in ctry, state]/[count(stage1 within ctry, state)]

           Ctry=A, State=B : Total Stage 1 = 3

           Ctry=A, State=B: Total Stage 3 = 1

       

      hence, value = 1/3

       

       

       

      Help very much appreciated.

      AccountCtryStateStatusstage1 to stage 3stage2 to stage3New Field
      A1ABstage21/31/21/2
      A2ABstage31/31/21
      A3ABstage11/31/21/2
      A4ACstage10/20/10/1
      A5ACstage20/20/10/1
      A6ADstage31/11/11/1
        • 1. Re: Tableau Hit Ratio by Country by State
          Norbert Maijoor

          Hi Hemalatha,

           

          Not sure but find my approach as reference below and stored in attached workbook version 9.3 located in the original thread

           

           

          1. Left stage 1: if [max Status]="stage3" then 1 else 0 end

          2. Right stage 1: {fixed [State]:count([Status])}

          3. Right stage 2: if {fixed [State]:count([Status])}>1 then {fixed [State]:count([Status])}-1 else  {fixed [State]:count([Status])} END

          4. Right stage 3: if [Status]<>"stage3" then {fixed [Crty],[State]:count([Status])}-1 else 1 END

           

          5. stage 1 to stage 3: str([left stage 1])+"/"+str([Right stage 1])

          6. stage 2 to stage 3: str([left stage 1])+"/"+str([Right stage 2 ])

          7. New field: str([left stage 1])+"/"+str([Right stage 3])


          Regards,Norbert