5 Replies Latest reply on Apr 18, 2013 7:49 AM by Allison Suarez

Filter IF Calculation

I am looking at serial numbers and associated cases that were created against a serial number.  What I want to see is all S/N's that have more that one case created against it within 20 days.  The furthest I got was I can distinguish which S/N's have more that one case but I can't use that calculation as a filter.

To add another layer of confusion, I want to also look at a reported issue.  So I want the SN and the Reported Issue to be the same, all within 20 days.

IF (COUNTD([SERVICE_REQUEST_ID])) >1 THEN 'Multiple' else 'Single' END

Has anyone accomplished this or have any ideas?

Thanks,

Allie

• 1. Re: Filter IF Calculation

Sounds like a Joe problem; actually a Joe teaching moment. A sample workbook is even attached. [Allie, always a good thing to do.]

--Shawn

• 2. Re: Filter IF Calculation

How about something like the attached?

The situation you have here an interesting one, and there are a variety of routes to accomplish what you are looking for. There are other routes that would enable greater analysis on this line of questioning, depending on your constraints, and desired interactions.

As for why could you not place a pill for this calculated field on the filter shelf, that is because the pill type is a Discrete Measure (a blue pill that is performing an aggregation). You could wrap the formula in a function like LOOKUP(<expression>,0) to turn it into a table calculation, enabling it to be on the filter shelf, but that is not efficient in computation for your situation. There a great many factors to Tableau, please get in touch if you would like to discuss in more detail.

1 of 1 people found this helpful
• 3. Re: Filter IF Calculation

Thanks, that gets me to the halfway point.  The other portion is ensuring that the dates the cases were created are within 14 days of each other.  So if there are two or more, can I have a second filter that looks for the dates?

Any suggestions on that one?

• 4. Re: Filter IF Calculation

That is a more complex situation, and you have only provided enough details on your logic to make this an ambiguous question. I would need more details on your business logic, to help you craft a formula to produce your desired results.

For example, if your data was the following:

Issue, Serial, Case, Date

Front, 1234, 5678, 1/1/2012

Front, 1234, 5678, 1/11/2012

Front, 1234, 5678, 1/21/2012

Front, 1234, 5678, 1/31/2012

What would you expect the result to be?

Edit: Looking at the sample data you provided, we would need a more representative set of data, because what you have provided does not have more than 1 day between cases.

Message was edited by: Joe Mako

• 5. Re: Filter IF Calculation

For the data below, I would expect to see the groups returned as individual results using the 14 day difference.

Issue     Serial    Case     Date

Front     1234     5678      1/1/2012

Front     1234     5679     1/11/2012

Front     1234     5679     1/11/2012

Front     1234     5680     1/21/2012

Front     1234     5680     1/21/2012

Front     1234     5681     1/31/2012