
1. Re: Chronic Conditions Count Help
MarcAnthony Di Biase May 29, 2018 1:34 PM (in response to Renee Coker)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 May 29, 2018 1:55 PM (in response to MarcAnthony Di Biase)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 May 29, 2018 2:00 PM (in response to MarcAnthony Di Biase)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 May 29, 2018 2:00 PM (in response to Renee Coker)diagnoses is interchangeable with chronic condition

5. Re: Chronic Conditions Count Help
Renee Coker May 29, 2018 2:42 PM (in response to MarcAnthony Di Biase)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 May 29, 2018 2:42 PM (in response to Renee Coker)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 SubCategory on the rows shelf and SubCategory on the filter.
For each Customer I count the number of Sub_Cat with the index() function (computing on CustomerId, SubCat restarting every CustomerId).
I get the Window_Max value of this count (same computing), and I get the number of preselect SubCat using the same Window_Max but now computing Table down.
Next I flag every Customer having X+ SubCat 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

Book(mc).twbx 1.2 MB


7. Re: Chronic Conditions Count Help
Renee Coker May 29, 2018 3:54 PM (in response to Michel Caissie)I've gone through your instructions twice, doublechecked 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 May 30, 2018 8:10 AM (in response to Michel Caissie)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 subcat
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()
SubCat 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 May 30, 2018 9:14 AM (in response to Renee Coker)My guess is that what you have the wrong computing of the table calculations.
On my finalView sheet if you rightclick 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 May 30, 2018 11:37 AM (in response to Michel Caissie)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 May 30, 2018 11:56 AM (in response to Renee Coker)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 AA10315, 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 May 30, 2018 12:51 PM (in response to Michel Caissie)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.

13. Re: Chronic Conditions Count Help
Renee Coker May 30, 2018 1:09 PM (in response to Renee Coker)sent

14. Re: Chronic Conditions Count Help
Renee Coker May 30, 2018 1:47 PM (in response to Renee Coker)sent again