1 2 Previous Next 26 Replies Latest reply on May 31, 2018 11:01 AM by Renee Coker

    Chronic Conditions Count Help

    Renee Coker

      I think I have been so overwhelmed by this project that my brain has stopped. Not to mention I'm rather new at this Tableau business. So please be patient with me as I think the answer is obvious, but I can't put my finger on it.

       

      I have a measure called Pivot Field Names.  It’s a list of chronic conditions that our members have. I have added this field to the filter card because we want to pick and choose different chronic conditions (as little or as many as we want). What the objective is how to get a count of members who have 3 or more of the preselected conditions on the filter.

       

      So if we select 18 out of 30 chronic conditions, how do I get a count of those members that have 3 or more of the 18 selected chronic conditions?

       

      I’m blocked from uploading any files due to possible PHI conflicts, so sharing a file – even a benign one isn’t an option. Not even a snapshot.  But I can describe in detail what I have on my cards if needed.

       

      Thank you for any help.

        • 1. Re: Chronic Conditions Count Help
          Marc-Anthony Di Biase

          Hi Renee,

           

          Before digging into your issue I'd like to recommend following this guide;Anonymize your Tableau Package Data for Sharing for future issues, as a packaged workbook is a lot easier to work with to actually deal with your data first hand.

           

          In this case however, this can be solved with a simple calculated field - I'm going to assume your data has a row for each chronic condition that relates to a member, and that a member can have multiple rows they appear in with different chronic conditions.

           

          IF({FIXED [Member] : COUNTD([Condition])} > 2) THEN [Member] END

           

          Let me know if this helps!

          • 2. Re: Chronic Conditions Count Help
            Renee Coker

            This is the formula I used.

             

            IF({FIXED [Subscriber ID] : COUNTD([Pivot Field Names])} > 2) THEN [Subscriber ID] END

            Calculation is valid.

             

            It came up as a Dimension, so I changed it to a Measure. Not sure what card to apply it to.  You are correct, the pivot field names are rows of conditions with a separate row for for the member -- the more conditions, the more rows the member has.

             

            Subscriber ID is the Member ID.

             

            Our firewall blocks all uploads to other applications. I can't ask for an exemption from the firewall. It's not permitted.

            • 3. Re: Chronic Conditions Count Help
              Renee Coker

              There's another issue. Number of Diagnoses needs to be a filter.  So we can filter for however many diagnoses we want the member to have.  For instance, we may choose 18 chronic conditions, but the filter should allow us to say we want the member(s) who have 3 of the diagnoses or 6 of the diagnoses. We need to be able to filter.  This is supposed to be an eligibility calculator, sort of a sandbox, to determine how many members qualify using different values as qualifiers. I hope that makes sense.

              • 4. Re: Chronic Conditions Count Help
                Renee Coker

                diagnoses is interchangeable with chronic condition

                • 5. Re: Chronic Conditions Count Help
                  Renee Coker

                  I converted it back to a dimension, but I am not having any luck with the formula. I've put the pill on the rows card, which I thought would be a step in the right direction, then I added a column total. I know what the correct column total should be, and it's not right.

                   

                  Shall I provide an accounting of my cards?

                  • 6. Re: Chronic Conditions Count Help
                    Michel Caissie

                    Renee,

                     

                    Here is a possible solution for this.

                     

                    In the attached, from the superstore ,I get the number of customer having at least X of the preselected Sub_Category.

                     

                    If you look on the sheet  ValidateData I first put the CustomerId and Sub-Category on the rows shelf and Sub-Category on the filter.

                     

                    For each Customer I count the number of Sub_Cat  with the index() function (computing on CustomerId, Sub-Cat  restarting every  CustomerId).

                    I get the Window_Max value of this count  (same computing),  and I get the number of preselect Sub-Cat using the same Window_Max  but now computing Table down.

                     

                    Next I flag every Customer having X+ Sub-Cat with

                    if [ID  count of preselected sub_cat] >=[Number of] then ATTR( [Customer ID] ) end

                    where Number of is a parameter allowing you to select whatever count you want

                     

                    Now I need to count a single row of the non null customers, with

                    if not isnull([Customer having X+ SubCat]) and first() = 0 then 1 else 0 end

                     

                    And to get the final number of Customer I get the Window_Sum of this last calculation

                    WINDOW_SUM( [Compute one row per Customer] )

                     

                    Finally, since I want a single value of this number I compute a filter if first() = 0 then 1 else 0 end

                     

                     

                    On the final view, I just duplicated the ValidateData sheet, moved 'Keep a single value' on the filter and keep the 1,  unselect Show Header on  CustomerId  and Sub_Cat, and keep only the measure with the value you need. Pay attention to this calculation, it is a nested calculation and you need to set the computing on each of the 4 calculation nested.

                     

                    Michel

                    • 7. Re: Chronic Conditions Count Help
                      Renee Coker

                      I've gone through your instructions twice, double-checked all calculated fields, and my result is 0. I even backtracked and cross referenced each reference to a calculated field.

                      • 8. Re: Chronic Conditions Count Help
                        Renee Coker

                        I'm unable to get this to work. I have virtually duplicated everything in my workbook. Here are your field names and formulas versus mine. Maybe you can see something, I cannot. There are two fields that I do not have anything for, so checking to see if they even need to be include.

                        Superstore Field Name

                        Superstore Formula

                        Eligibility Calculator Field Name

                        Eligibility Calculator Formula

                        Computer one row per Customer

                        if not isnull([Customer having 3+ SubCat]) and first() = 0 then 1 else 0 end

                        Compute one row per Member

                        IF NOT isnull([Member having 3+ Pivot Field Names])and first() = 0 then 1 else 0 END

                        Customer Filter (blue)

                        LOOKUP( MIN( [Customer ID] ),0 )

                        ???

                         

                        Customer having 3+ SubCat (blue)

                        if [ID  count of preselected sub_cat] >=[Number of] then ATTR( [Customer ID] ) end

                        Member having 3+ Pivot Field Names

                        IF [ID Count of Preselected Pivot Field Names] >=[Number of] then ATTR([Subscriber ID]) END

                        ID count of preselected sub-cat

                        WINDOW_MAX( [Sub_cat Counter] )

                        ID Count of Preselected PFN

                        WINDOW_MAX([Pivot Field Names Counter])

                        Keep a single value (blue)

                        if first() = 0 then 1 else 0 end

                        Keep a single value

                        if first() = 0 then 1 else 0 end

                        Nb of preselected

                        WINDOW_MAX( [Sub_cat Counter] )

                        Number of Preselected

                        WINDOW_MAX([Pivot Field Names Counter])

                        Number of Customer having X+ SubCat

                        WINDOW_SUM( [Compute one row per Customer] )

                        Number of Member having X+ PVN

                        WINDOW_SUM([Compute one row per Member])

                        Sub_cat Counter

                        index()

                        Pivot Field Names Counter

                        INDEX()

                        Sub-Cat filter (blue)

                        index()

                        ???

                         

                        Number of (parameter)

                        Number of, Integer, 4, Automatic, All

                        Number of

                        Number of, Integer, 4, Automatic, All

                         

                        Would it be helpful if I posted some benign data like this in an email, like five rows of data?

                        • 9. Re: Chronic Conditions Count Help
                          Michel Caissie

                          My guess is that what you have the wrong computing of the table calculations.

                           

                          On my finalView sheet if you right-click the green pill  "Number of...." and click Edit Table calculation you get

                           

                          In the Nested Calculations  dropdown you have a list of 4 nested calculations. Make sure that you set the Compute using individually on the 4 of them

                           

                          I suggest that you start a new workbook using superstore and try to recreate what I did. If you don't get the numbers, post your package workbook (.twbx) and I'll check what you have missing.

                          • 10. Re: Chronic Conditions Count Help
                            Renee Coker

                            I'm reworking through this at your suggestion, but I have a question. What is the difference between the Nb of perselected and ID count of preselected sub_cat. I'm not finding both in your instructions above other than is in formula and the other is not.  Thank you.

                            • 11. Re: Chronic Conditions Count Help
                              Michel Caissie

                              If you look on the ValidateData sheet, you can see that the ID count of preselected sub_cat  is a per Customer value.  It is the number of sub_cat from the preselected list that the Customer have.

                               

                              While the Nb of preselected is the total number of sub_cat  selected in the filter.

                               

                              For example if you select in the filter  only  Accessories, Appliances and Art then  Nb of preselected  is  3.

                              And if you look at CustomerID  AA-10315,  the  ID count of preselected sub_cat  is  2   because this customer have orders only for Accessories and Appliances.

                               

                              If you set the parameter to  1 or  2  then this customer will be accounted  and the first row of the customer will get a 1 on the  Compute one row per Customer.

                               

                              The total of all those 1s  (in Compute one row per Customer)  give you the  Number of Customer having X + SubCat

                              • 12. Re: Chronic Conditions Count Help
                                Renee Coker

                                I was very careful in duplicating your steps and fields verbatim, even checking Table Down using the Edit Table Calculation. My table has over 12858 subscribers all with varying degrees of chronic conditions, but my result is 1. All the other fields resulted in 0 or varying degrees of over 200,000. My answer should be 1577. That's how I know it's working.

                                 

                                The only way I can share the workbook with you is by emailing it to you after I have created a benign data source. Our firewall blocks me from uploading.

                                1 2 Previous Next