9 Replies Latest reply on Sep 28, 2016 11:07 AM by pooja.gandhi

# How do you Count only certain items?

I have a database where the data looks something like the below table.  I want to count ONLY the number of clients who bought BOTH items A and C during a quarter.  I have tired various calculations IF, ELSEIF, AND etc. but am not finding a way to count only those getting both items, it keeps count that plus those getting only or only C.  Anyone know how to do this?

Client IDDate Item Payment
1001/1/2016Ax
1102/1/2016By
1202/1/2016Cx
1303/1/2016Dx
1103/1/2016Cy
1402/15/2016By
1002/1/2016Cx
1202/15/16Ax
1304/1/2016Dx
1504/1/2016Ay
• ###### 1. Re: How do you Count only certain items?

There might be other ways to do this. One way to do it would be to create a calculation like this:

Drag it to filters, select true, drag quarter datepart to rows and countd(ID) on text as shown in the screenshot above. Based on your sample data it is counting client ID's 100 and 120 because they both show items 'A' and 'C' in a single quarter.

Hope this helps!

• ###### 2. Re: How do you Count only certain items?

Thank you for this Pooja.  I just tried it, and unfortunately it counted those that only counted A or C and sometimes both but left out a lot of he cases where the client got both A and C.

If you think of something else to try that would be great.

Thank you, Anne

• ###### 3. Re: How do you Count only certain items?

Anne,

Pooja Gandhi 's calculation will work in cases where the client only has one record for A and one record for C (or two records for A or two records for C), but in cases where there are any combination of 3 or more records (say they bought A twice and C three times), then the SUM aggregation will count extra (in the example, you'd get 5).

So, you might tweak it just a bit to:

{FIXED [Client ID] : COUNTD(IF [Item] = 'A' OR [Item] = 'C' THEN [Item] END)} = 2

That will count the distinct number of items that are either A or C and will always give you 2 if both are present (and 0 or 1 if none or only one is present).

Hope that helps!

Joshua

• ###### 4. Re: How do you Count only certain items?

You could use something like this:

IF { FIXED [Client ID], [Date (Quarter)] :

MAX(IF [Item] = 'A' THEN 1 ELSE 0 END) +

MAX(IF [Item] = 'C' THEN 1 ELSE 0 END) } = 2

THEN 1 ELSE 0 END

I have only an expired version of Tableau 9 and therefore had to save DATETRUNC('quarter',[Date]) as [Date (Quarter)] because only dimensions were allowed in LoD. I don't know for sure but think calculations as dimensions in LoD are supported today and therefore could be written like this:

IF { FIXED [Client ID], DATETRUNC('quarter',[Date]) :

MAX(IF [Item] = 'A' THEN 1 ELSE 0 END) +

MAX(IF [Item] = 'C' THEN 1 ELSE 0 END) } = 2

THEN 1 ELSE 0 END

• ###### 5. Re: How do you Count only certain items?

COUNTD is probably more effective (and definitely shorter) than 2x MAX I just shared

I think Anne wants this by quarter and therefore think she needs it as a second dimension.

• ###### 6. Re: How do you Count only certain items?

Yes, I just re-read that and saw it should be by quarter also, so:

{FIXED [Client ID], [Quarter] : COUNTD(IF [Item] = 'A' OR [Item] = 'C' THEN [Item] END)} = 2

Thanks for catching that kettan!

-Joshua

• ###### 7. Re: How do you Count only certain items?

Thank you Joshua.  So I just tried what you suggested but no results at all produced this time.  So I then tried changing it from COUNTD to COUNT and got the same results as I had with what Pooja's formula gave me. hmmm?

• ###### 8. Re: How do you Count only certain items?

Kettan, this worked.  It may be longer but it gave me what I needed.  Thank you very much.

And thank you to Pooja and Joshua for the help as well.