7 Replies Latest reply on Nov 20, 2018 8:31 AM by Vesko Filipov

    Some help with one visual

    Vesko Filipov

      Hello everyone,

       

      I'm working on tableau almost an year and this is my first discussion here. For now i managed to made my tasks with lot of reading and testing but this time i need from some advises.

      The idea of what i want sounds very easy The example workbook( version 2018.3.0) is also attached. In summary we have :

      - brands ( column "brand")

      - sub brands ( column "productName")

      - rating ( column "Combined Rating")

       

      I need to create a bar chart visual based on brands with Table calculation Percent of total and compute using Rating ranges( this is a pill on color marks). Rating ranges is an average( rating) for every sub brands.

      Picture example :

      PicExample.png

      The problem is the data source structure because every raw is multiplied based on comments :

      - respondent ID ( column "TextID")

      - comment ( column "Text")

      - part of the comment ( column "Text Part")

       

      I realized that its not possible just to use AVG([Combined Rating]) because i need the avg rating per unique TextID and also productName ...

      Picture example data source:

      Pic2Example.jpg

       

      If i use this calculation the percent of total is 170%

      "IF {fixed [TextID],[productName]: AVG([Combined Rating])}>=4.4 THEN "% Over 4.4"

      elseif {fixed [TextID],[productName]: AVG([Combined Rating])}<4.4 and {fixed [TextID],[productName]: AVG([Combined Rating])}>=3.4 THEN "% 3.4 - 4.4"

      else "% under 3.3"

      END"

       

      And if its only on product level the percent of total is ok 100% but this is not the correct values

      IF {fixed [productName]: AVG([Combined Rating])}>=4.4 THEN "% Over 4.4"

      elseif {fixed [productName]: AVG([Combined Rating])}<4.4 and {fixed [productName]: AVG([Combined Rating])}>=3.4 THEN "% 3.4 - 4.4"

      else "% under 3.3"

      END

       

       

      This is my 3rd day with this problem and I will be happy for any suggestions and ideas.

       

      Best Regards,

      Vesko

        • 1. Re: Some help with one visual
          Mavis Liu

          Hi Vesko,

           

          This is because you have the same product name across multiple respondents/text ids. So you should do your count distinct based on a combination of these two fields.

           

          Please see the attached.

           

          Thanks,

           

          Mavis

          • 2. Re: Some help with one visual
            Norbert Maijoor

            Hi Vesko,

             

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

             

            brand.jpg

             

            1. D1. Calc Ranges for Unique Rating on Respondent&Product Level

             

            IF {fixed [brand],[productName]: AVG([Combined Rating])}>=4.4 THEN "% Over 4.4"

            elseif {fixed [brand],[productName]: AVG([Combined Rating])}<4.4 and {fixed  [brand],[productName]: AVG([Combined Rating])}>=3.4 THEN "% Between 3.4 and 4.4"

            else "% under 3.3"

            END

             

            2. M1. countd Product Name: countd([productName])

             

             

            3. Drag required objects to the indicated locations

             

             

            Hope it helps.

             

            Regards,

            Norbert

            • 3. Re: Some help with one visual
              Vesko Filipov

              Hi Mavis,

               

              Thanks for the suggestion. I was never been so close to the solution but we still are not there...

              I've checked the results and you have 2 products for "% under 3.3"and when I'm doing the calculations manual in excel only one product is in this target ( Name 1)

              If I read correctly the results in your workbook these two products are Name 6 and Name 9 and this is because the average is based on each raw?

               

              Best Regards,

              Vesko

              • 4. Re: Some help with one visual
                Vesko Filipov

                Hi Norbert,

                 

                Thanks for the suggestion. Please check below what it would be great if managed to take as a result:

                NorbertSuggestionData.jpg

                 

                Your average is based on all raw. In green highlight is the result i need.

                 

                Thanks for taking your time in this problem.

                 

                Best Regards,

                Vesko

                • 5. Re: Some help with one visual
                  Mavis Liu

                  Hi Vesko,

                   

                  In your previously attached workbook I can see that I have an extra value of text ID '3' under product name 1:

                   

                  2018-11-15_09h48_58.png

                   

                  So this could be the reason as to why it's different?

                   

                  Thanks,

                   

                  Mavis

                  • 6. Re: Some help with one visual
                    Vesko Filipov

                    Hi Mavis,

                     

                    These are the screenshots from your suggested workbook:

                    Pic4Example.jpgPic5Example.jpg

                     

                    If I understand correctly the results you classify products Name 6 and Name 9 into range "% under 3.3"

                    And the result i need is only Name 1  but let me know if my checks are not correct.

                     

                    Best Regards,

                    Vesko

                    • 7. Re: Some help with one visual
                      Vesko Filipov

                      Hi Tableau community,

                       

                      I still trying to build this viz without success!

                      For now I create only a workaround with second data source but in future it would be great if i can avoid this data prep - the project will need daily update and any manual work is not a good idea

                       

                      You can find attached the workbook with the second data source with point to check what is the correct output result.

                       

                      Thanks in advance!

                      Best Regards,

                      Vesko Filipov