11 Replies Latest reply on Mar 7, 2017 8:50 AM by Dhruv Gupta

# Count Dates That Include 1 Value But Not Another

Hey guys,

I have attached a workbook (Tableau 10) which includes a sample data set.

I can easily calculate the amount of times that each value of 'Qty' appears within the data set.  What I want to do now is filter out those days where there is more than 1 value for 'Qty'.

So how can I count those days that were exclusively 50, for example?

• ###### 1. Re: Count Dates That Include 1 Value But Not Another

Hi Steve

You can add an IF statement and then drag it to the vis with sum

NOTE - in you data the value is a string hence the '50'

if [Qty]='50' then 1 else 0 END

Let me know if this helped

Jim

• ###### 2. Re: Count Dates That Include 1 Value But Not Another

Hi Steven,

I think below image should be sufficient enough to get the count of Qty as per its different category.

Hope this Helps!!

Thanks,

Dhruv

• ###### 3. Re: Count Dates That Include 1 Value But Not Another

Unfortunately, neither of these solutions work with the way that my data is shaped.  See mocked up table below -

Date
Qty
04/01/201650
04/01/201650
05/01/201650
05/01/2016

25

06/01/201625
06/01/201625

So what I should see is a count of 1 for '50' and a count of 1 for '25'.  the 4th Jan should only count the value once.  This is why I used COUNTD on Date.  Given this table, the 5th Jan shouldn't be counted at all.

I hope this outlines my problem better.

• ###### 4. Re: Count Dates That Include 1 Value But Not Another

I'd work with a Parameter

- Create Parameter

- Create CL with

IF [Qty]=[Parameter 1] then 1 else 0 END

See attachment.

-Rendy

Biztory

• ###### 5. Re: Count Dates That Include 1 Value But Not Another

Hi Rendy,

Thanks for the response.  This doesn't work either I'm afraid.  This will count all instances of the Date which contains the value "50", but it doesn't exclude any dates that also contain either 'null' or '25'.

• ###### 6. Re: Count Dates That Include 1 Value But Not Another

Hi Steven,

Refer below image.

Hope this Helps!!

Thanks,

Dhruv

• ###### 7. Re: Count Dates That Include 1 Value But Not Another

Hi

see if this works

Count 50 >>If [Qty]='50' then (iif([Qty]='50',1,0)) end

then filter for Count 50 at least 2

Jim

• ###### 8. Re: Count Dates That Include 1 Value But Not Another

Hi Steven,

Think I misunderstood you then.

See attached workbook (Sheet 2), let's you filter on All, 0, 0 & 25, 0 & 50, 25 & 50, 25, and 50.

You can see how it's done, and take what you need

• ###### 9. Re: Count Dates That Include 1 Value But Not Another

Almost perfect!

I used your Calc but was having issues around the 'Null' values.  Converted those to 0s and based the Calc on the conversion.  Now I have exactly what I need.  Thank you for the work.

My amended workbook is attached (I think).

• ###### 10. Re: Count Dates That Include 1 Value But Not Another

Hi again Rendy,

This solution also has exactly what I need.  Dhruv Gupta's solution was simpler though, with just the two calcs.

Really appreciate all the help

• ###### 11. Re: Count Dates That Include 1 Value But Not Another

You are Welcome Steven.

Thanks,

Dhruv