CODEWITHSUNDEEP
tableau-api
A. Ganady
A. Ganady

Reputation: 407

Tableau: Using LOD to count distinct number of records based on a condition (10.2)

This question is similar to others but I'm having issues generating the correct output. I appreciate any assistance!

The goal is for a dimension, count the number of distinct records if the string contains "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

Workbook with Data: https://community.tableau.com/message/729431

Upvotes: 1

Views: 4761

Answers (1)

S. User18
S. User18

Reputation: 712

From the workbook and the "desired output" from the link, you are wanting to fix on [User] and MONTH([Date]) (vs fixing on [Subject] as in the code tried in the question.

This is the field I created that 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.

Note that that matches the desired output. If instead the grand totals should 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))

Upvotes: 3

Related Questions