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
17,328 views

1 answer to this question.

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,560 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,240 points
9,839 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, 2019 in Tableau by anonymous
• 33,030 points
3,305 views
0 votes
1 answer

Dynamically set the value of a filter based on a parameter selection in use to swap sheets

Here's a simplified example of the calculated ...READ MORE

answered Apr 29 in Tableau by anonymous
• 1,380 points
2,096 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 9, 2018 in Tableau by ffdfd
• 5,550 points
4,769 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,240 points
3,070 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 6, 2018 in Tableau by Atul
• 10,240 points
5,611 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,240 points
11,668 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, 2019 in Tableau by Cherukuri
• 33,030 points
2,909 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,560 points
791 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,560 points
6,809 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP