How to use LOD to count distinct number of records based on some condition?

0 votes

Goal: For a dimension, count the distinct numbers of records if the string has "List Email", otherwise count all records.

I've tried:

IF CONTAINS(ATTR([Subject]), "List Email:")
then ((COUNTD([Subject])))
Else COUNT([Subject])
END

(That works only if the dimension is in the view)

and

CASE CONTAINS(ATTR([Subject]), "List Email:")
WHEN TRUE   then ATTR({ FIXED  [Subject] : COUNTD([Subject])})
ELSE ATTR({ FIXED  [Subject] : COUNT([Subject])})
END
User Dec Jan
Total 3 17
Thomas 1 9
John 2 8
Apr 9, 2018 in Tableau by ghost
• 1,790 points
2,388 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
0 votes

From the desired output it seems that you want to fix on [User] and MONTH([Date]) (vs fixing on [Subject] as in the code tried in the question).

This field will gives you the desired output. :

User Emails for Month:
{ FIXED [User], MONTH([Date]) : 
    COUNTD(IIF(CONTAINS([Subject],"List Email"),[Subject],NULL))
    +
    COUNT(IIF(NOT CONTAINS([Subject],"List Email"),[Subject],NULL))
}

For each User and Month, count distinct emails that contain List Email and add to that all emails that don't contain List Email.

But if all you need is the grand totals to be 3 for December and 16 for January (i.e., count distinct subject regardless of the user), then LOD calculation is not necessary:

# Emails:
COUNTD(IIF(CONTAINS([Subject],"List Email"),[Subject],NULL))
+
COUNT(IIF(NOT CONTAINS([Subject],"List Email"),[Subject],NULL))
answered Apr 9, 2018 by xyz
• 1,550 points

Related Questions In Tableau

0 votes
1 answer

How to color code cells of a column based on the text value in Tableau

You can use the following steps to ...READ MORE

answered Mar 27, 2018 in Tableau by Atul
• 10,220 points
1,095 views
0 votes
1 answer

SQL query to count number of distinct products

Hi Preethi, Use below SQL query, SELECT CUST_ID, COUNT(DISTINCT ...READ MORE

answered Jun 10 in Tableau by anonymous
• 15,140 points
10 views
0 votes
1 answer

How to calculate the percent of records within a group in tableau?

 You can click the measure SUM(Number of Records) and ...READ MORE

answered May 8, 2018 in Tableau by Atul
• 5,490 points
73 views
0 votes
1 answer

Is it possible to join tables on the basis of some calculated field in Tableau?

@Ammy999, You can not perform join in ...READ MORE

answered May 16, 2018 in Tableau by Mathew1810
65 views
0 votes
1 answer

how to get the count of non zero values in a row

In first part you should solve the ...READ MORE

answered Apr 4, 2018 in Tableau by Atul
• 10,220 points
92 views
0 votes
1 answer

How can I count the aggregate function in Tableau

Create two calculated fields one for 100's ...READ MORE

answered Apr 5, 2018 in Tableau by Atul
• 10,220 points
1,063 views
0 votes
1 answer

How to count occurrence of value and percentage of a subset in tableau public?

Although it sounds like a fairly easy ...READ MORE

answered Jun 5, 2018 in Tableau by Atul
• 10,220 points
1,679 views
0 votes
1 answer

How to aggregate and count the occurrence of TRUE and FALSE?

Hi, you can do any of below ...READ MORE

answered Mar 12 in Tableau by CHERUKURI
• 15,140 points
57 views
0 votes
1 answer

How to use tableau to searh for text within entire data

You can use a pivot table in ...READ MORE

answered Apr 4, 2018 in Tableau by xyz
• 1,550 points
30 views
0 votes
1 answer

How to group bins in a tableau histogram?

One of the simple way is by ...READ MORE

answered Apr 3, 2018 in Tableau by xyz
• 1,550 points
643 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.