Need to count boolean values in Tableau

Question

I'm using mongo with Tableau and have a boolean called "verified" that shows as true vs false.

Each user can add "certifications" to his/her record, then we go in with an admin tool and flag the cert as verified:true or verified:false. I want to show a simple table that has the number of certifications for each user, then another column with the number verified.

Currently I'm using "COUNTD([Certifications.Verified])" to count the number of verified but I don't think it's accurately counting.

This is just counting if the sub-schema of "verified" exists with a true or false state so the numbers are not accurate. Note, in some cases this node doesn't exist and is shown as a null.

I need to way to count if the the verified=true then 1 if no verified node exists or verified:false then 0.

How do I add the logic to count this accurately in Tableau?

Update: Thanks for the Mongo queries but I'm looking for Tableau custom fields to show this.

Answer 1

You're going to want to use the $cond pipeline operation, within your .aggregate() operator. It'll allow you to specify what you would like returned based on a conditional, which in your case would be the Verified field. I don't know how your data is structured, but I would imagine using something like this:

$sum: { $cond: ["$Certifications.Verified", 1, 0] }

If Verified is true for that certification, it will return a 1 which will be accounted for in the $sum operator. Whether you want to use something like $group operator or a $project to create this summed field will depend on your preference/use case.

Answer 2

You can use this to return count.

schemaName.find({Certifications.Verified : true}).count(function (error,count) { 
  console.log(count);
});

it return non-zero value(no of document satisfied condition) if certificate verified = true is exist otherwise it return 0

Answer 3

Replacing whatever your table's key is with RowID:

COUNTD(IIF([Certifications.Verified]=1, RowID, NULL))

Answer 4

COUNTD() is useful but can be computationally inefficient on large data sets, so don’t use COUNTD() in situations where a simpler, faster aggregation function will work equally well.

If you simply want to know how many records satisfy From Tableau, just use SUM(INT(<condition>)) The INT() type conversion function converts True to 1 and False to 0. So if the level of detail of your data has one record per THING, and you want to know how many THINGs satisfy your condition, just using SUM(INT(<condition>)) will do the trick, faster than using count distinct on record ids.

There are even some data sources, like MS Access, that don’t implement COUNTD()

Bottom line,

• SUM(INT()) is the simplest way to count records that satisfy a condition
• COUNTD() is very flexible and useful, but can be slow. For large data sets or high performance environments, consider alternatives such as reshaping your data.

BTW, similar advice applies to LOD calculations. They are very useful, and flexible, but introduce complexity and performance costs. Use them when necessary, but don’t use them when a simpler, faster approach will suffice. I see a lot of people just use FIXED LOD calcs for everything, presumably because it seems a lot like SQL. Overdone, that can lead to a very brittle solution.