1 2 Previous Next 20 Replies Latest reply on Apr 23, 2018 9:26 AM by Deepak Rai

    Count by Group question.

    prashant gandhi

      Hi, I am very new to tableau. I have created a tree map that shows health plans based on their percent claims. percent claim is a calculated field. If the percent claim is above 90 then the health plan box shows green otherwise it shows red. So the tree map has bunch of green and red boxes each for 1 health plan. This is a working piece.

       

      PercentClaim is a complex calculation which is dependent on the table fields that I got from SQL Server DB. I am using Tableau desktop 10.5

       

      What I want is an upper level view which demonstrates only green/red box and shows the count of health plans that fall in each.

       

      For example currently what I have in tree map is:

       

      HealthPlan  PercentClaims   Color

      ABC               91                    Green

      DEF               85                     Red

      GHI                60                    Red

      JKL               100                   Green

      MNO             94                     Green

       

      What I am looking for is a tree map that shows:

       

      Color        CountofHealthPlans

      Green           3

      Red              2

       

      I think some kind of groupby or filter would be required in conjunction with count(healthplan). Thanks a lot.

        • 1. Re: Count by Group question.
          Martin Dunleavy

          { FIXED [Color] : COUNT([HealthPlan]) }

          • 3. Re: Count by Group question.
            prashant gandhi

            Hi Deepak, Thanks, this is exactly what I am looking for. But, I don't understand how would I go about it using your suggestion. These are calculated data! I have attached the package file if you can show me. Thanks!

            • 4. Re: Count by Group question.
              Deepak Rai

              Please extract your data and reattach

              • 5. Re: Count by Group question.
                prashant gandhi

                Sorry but can you please let me know how to do that? I am very new to tableau so I don't know what you are asking for. Aren't you able to open the package and see data/worksheet that I sent? Thanks.

                • 6. Re: Count by Group question.
                  Deepak Rai

                  You have live Connection in ur wb. Right click on ur datasource and click extract

                  • 7. Re: Count by Group question.
                    prashant gandhi

                    Let see if this works. Is this what you are looking for?

                     

                    I have created table testtbl with following ddl/dml. As you can see the healthplans ABC and JKL should be green color since they are above 90% and DEF, GHI, MNO should be red color since they are below 90%.

                     

                    Desired output should be a treemap with 2 colors:

                     

                    Green 40% covered and red 60%.

                    • 8. Re: Count by Group question.
                      prashant gandhi

                      testtbl ddl/dml:

                       

                       

                      table testtbl (healthplan varchar(10), claimkey varchar(10), receiveddt datetime, reportdate datetime)



                      into testtbl values('ABC', '111', '2018-01-01', '2018-01-02')


                      into testtbl values('ABC', '222', '2018-01-01', '2018-01-10')


                      into testtbl values('ABC', '333', '2018-01-01', '2018-01-12')


                      into testtbl values('ABC', '444', '2018-01-01', '2018-01-13')



                      into testtbl values('DEF', '555', '2018-01-01', '2018-01-20')


                      into testtbl values('DEF', '666', '2018-01-01', '2018-01-10')


                      into testtbl values('DEF', '777', '2018-01-01', '2018-01-12')


                      into testtbl values('DEF', '888', '2018-01-01', '2018-01-13')



                      into testtbl values('GHI', '999', '2018-01-01', '2018-01-20')


                      into testtbl values('GHI', '000', '2018-01-01', '2018-01-30')


                      into testtbl values('GHI', '123', '2018-01-01', '2018-01-12')


                      into testtbl values('GHI', '234', '2018-01-01', '2018-01-13')



                      into testtbl values('JKL', '345', '2018-01-01', '2018-01-02')


                      into testtbl values('JKL', '456', '2018-01-01', '2018-01-02')


                      into testtbl values('JKL', '567', '2018-01-01', '2018-01-02')


                      into testtbl values('JKL', '678', '2018-01-01', '2018-01-02')



                      into testtbl values('MNO', '124', '2018-01-01', '2018-01-20')


                      into testtbl values('MNO', '125', '2018-01-01', '2018-01-30')


                      into testtbl values('MNO', '126', '2018-01-01', '2018-01-25')


                      into testtbl values('MNO', '127', '2018-01-01', '2018-01-02')



                      * FROM testtbl

                       

                      • 9. Re: Count by Group question.
                        prashant gandhi

                        create table testtbl (healthplan varchar(10), claimkey varchar(10), receiveddt datetime, reportdate datetime)

                        insert into testtbl values('ABC', '111', '2018-01-01', '2018-01-02')
                        insert into testtbl values('ABC', '222', '2018-01-01', '2018-01-10')
                        insert into testtbl values('ABC', '333', '2018-01-01', '2018-01-12')
                        insert into testtbl values('ABC', '444', '2018-01-01', '2018-01-13')
                        -- PROCESS 15 DAYS OR LESS: 100%

                        insert into testtbl values('DEF', '555', '2018-01-01', '2018-01-20')
                        insert into testtbl values('DEF', '666', '2018-01-01', '2018-01-10')
                        insert into testtbl values('DEF', '777', '2018-01-01', '2018-01-12')
                        insert into testtbl values('DEF', '888', '2018-01-01', '2018-01-13')
                        -- PROCESS 15 DAYS OR LESS: 75%

                        insert into testtbl values('GHI', '999', '2018-01-01', '2018-01-20')
                        insert into testtbl values('GHI', '000', '2018-01-01', '2018-01-30')
                        insert into testtbl values('GHI', '123', '2018-01-01', '2018-01-12')
                        insert into testtbl values('GHI', '234', '2018-01-01', '2018-01-13')
                        -- PROCESS 15 DAYS OR LESS: 50%

                        insert into testtbl values('JKL', '345', '2018-01-01', '2018-01-02')
                        insert into testtbl values('JKL', '456', '2018-01-01', '2018-01-02')
                        insert into testtbl values('JKL', '567', '2018-01-01', '2018-01-02')
                        insert into testtbl values('JKL', '678', '2018-01-01', '2018-01-02')
                        -- PROCESS 15 DAYS OR LESS: 100%

                        insert into testtbl values('MNO', '124', '2018-01-01', '2018-01-20')
                        insert into testtbl values('MNO', '125', '2018-01-01', '2018-01-30')
                        insert into testtbl values('MNO', '126', '2018-01-01', '2018-01-25')
                        insert into testtbl values('MNO', '127', '2018-01-01', '2018-01-02')
                        -- PROCESS 15 DAYS OR LESS: 25%

                        SELECT * FROM testtbl

                        • 10. Re: Count by Group question.
                          Deepak Rai

                          pLesae attach .twbx. The attached .twb wont open,Always attach .twbx

                          • 11. Re: Count by Group question.
                            ShivaRam Chennapragada

                            Prashanth- follow these instructions to create a Packaged Workbook and attach.

                             

                            Packaged Workbooks

                            • 12. Re: Count by Group question.
                              prashant gandhi

                              Sorry. attached. Thanks.

                              • 13. Re: Count by Group question.
                                Deepak Rai

                                Will this Work?

                                 

                                • 14. Re: Count by Group question.
                                  prashant gandhi

                                  Thanks Deepak. I have couple of questions.

                                   

                                  Doesn't tableau allow aggregation on multiple grouping? The green box looks right with 2. But the reds are 1,1,1 instead of 3?

                                   

                                  Also, when I try to bring both the worksheets on dashboard, I was hoping that if the user clicks green color on your sheet, it would display all the details about the green boxes of the treemap that I originally have (click filter sort of).if the user would click on red box, they will get the red part of treemap highlighted with all the red health plans. That's what generally senior executives would be interested in. wouldn't tableau allow such things? Thanks again.

                                  1 2 Previous Next