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,988 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,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,240 points
1,350 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
• 17,870 points
15 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,530 points
105 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
70 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
106 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,240 points
1,225 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
2,006 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
• 17,870 points
63 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
41 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
769 views