5 Replies Latest reply on Apr 15, 2019 7:18 AM by Ewald Hofman

# Subquery: count of a count

Hello,

I'm quite new to Tableau and I wanted to know if this is possible to do.

I have two tables: one with the user information and another one with the purchase information. Every user can purchase the product as many times as they want to. I want to count the number of people that have purchased the product X number of times. So, in SQL, I need to use a subquery which is:

select purchases, count(purchases) from (select count(distinct p.id) as purchases from account_purchase p left join account_customuser u on u.id = p.user_id group by u.id) as foo group by purchases order by purchases;

The result it gives is:

purchases | count

-----------+-------

1 |  1263

2 |   429

3 |   16

4 |    8

5 |    2

6 |    1

7 |     2

8 |     3

9 |    3

However, in Tableau Desktop, I cannot find a way to do that. I have been able to calculate the subquery and show the number of purchases each user has made. But then, I would like to take this data and group by the number of purchases in order to determine the number of people that have bought the product 2 times, 3 times, etc.

Is there a way to do that?

Thank you very much for your help.

• ###### 1. Re: Subquery: count of a count

You can create a LOD expression (Overview: Level of Detail Expressions - Tableau ) to calculate the the number of products that they bought

{FIXED [Customer Name] : COUNTD([Product Name]) }

You can extend the formula to detect if a customer has bought N number of products (in this case 6)

{FIXED [Customer Name] : MAX(IF {FIXED [Customer Name] : COUNTD([Product Name]) = 6 } THEN 1 ELSE 0 END) = 1}

• ###### 2. Re: Subquery: count of a count

Hi,

Thank you very much for your reply.

"You can extend the formula to detect if a customer has bought N number of products (in this case 6)"||

But we would like to have a new table showing how many customers have bought N products (a count), rather than show individually which customers have done so. And we don't want to hardcode the values.

Is this something that's possible in Tableau?

Thank you very much. Looking forward to your reply.

• ###### 3. Re: Subquery: count of a count

Do you mean something like this?

• ###### 4. Re: Subquery: count of a count

Hi,

Thank you very much for your reply. However, I have Tableau Desktop 10.4 and I cannot open the file you sent me because it's a new version. I tried converting it to an older version using this link: Data + Science but it does not work. Could you please send me the 10.4 version please?

I would really appreciate it.

Thank you very much.

• ###### 5. Re: Subquery: count of a count

Now in 10.4 version