CODEWITHSUNDEEP
tableau-api
Bhavana
Bhavana

Reputation: 47

Tableau: Distinct count of a field which occurs more than once

I have a field customer_id and I need to track the number of unique users and repeat users. For example the table is as below:

customer_id
11
22
33
11
44
22

Here, the no. of unique users is 4 (11,22,33,44) and number of repeat users are 2 (11,22).

I am calculating unique users as COUNTD([customer_id]).

How can I calculate repeat users? It is basically the distinct count of the values which appear more than once. I tried with the following expression:

COUNTD(IF COUNT([customer_id]) > 1
THEN [customer_id]
END)

but I'm getting an error: Cannot mix aggregate and non-aggregate arguments comparisons or results in IF expressions

How else can I calculate the repeat users?

Thanks in advance.

Upvotes: 0

Views: 1313

Answers (2)

AnilGoyal
AnilGoyal

Reputation: 26218

A simple alternative to Fabio's answer can also do the job. Just create a calculated field

COUNT([customer id]) >1

and add this to filter shelf.

You can filter out false candidates to remove unique users and taking returning customers only.

Upvotes: 1

Fabio Fantoni
Fabio Fantoni

Reputation: 3167

According to your filter needs, you can rely on LOD using FIXED/INCLUDE:

{ FIXED [Customer Id] : if sum({ FIXED [Customer Id] : COUNT([Customer Id])}) > 1 then 1 end }

Basically, in the inner LOD you count the occourrences, and then you just take in consideration records having 2+ (>1) of them:

enter image description here

Upvotes: 1

Related Questions