Tableau calculations for items opened prior to a specific date and closed after that date

Question

I have two tables in Tableau: (A) one is a list of dates from the current day to 400 days prior and the other (B) is a list of ticket IDs along with open and closed dates for each ticket.

What I'm trying to do is determine the number of tickets that were active on each date in the date table. For each record in table A, I need the number of tickets that were opened on or before that date and were closed on or after that date.

In Excel, I would use COUNTIFS. In PowerPivot/Power BI, I would use a combination of COUNTROWS on table B with an EARLIER reference to table A. I'm pretty new to Tableau, so I'm thinking that I'm getting stuck trying to do something similar to Excel or DAX when Tableau probably has its own way.

I'm completely stumped. I tried finding an equivalent of what I know from other tools, but there doesn't seem to be one; and I'm sure that getting hung up on how Excel works here.

Answer 1

You can write a calculated field to check if the day on the date table falls inside the ticket id's open and closed date range.

Lets assume:

• [date] is the date of your table A
• [open date] is the date of id open table B
• [closed date] is the closing date of table B

Create a calculated field:

//This checks if the date is greater than the open date and less than the closed date and returns the ticketID if true
IF [date] >= [open date] AND [date] <= [closed date] THEN [ticketID] END

From here, in a new worksheet, you can use this calculation and [date](from table A) to make a bar chart or table ect... of open tickets for a given date.

Note: Be sure your date data types match.

Note 2: For this approach, you need to associate each row in your calendar table with each row in your ticket table, with either a logical (noodle) relationship or a physical join. In either case, to get the effect of a cross join, the join condition needs to be always true, something like 1=1. This will obviously expand the data set, each resulting data row represents one ticket on one particular day. So can have performance implications on very large data sets.

Answer 2

Note - The following answer describes an approach for when you only have the table of tickets (your table B) If you have a calendar table (your table A) then the approach by @Muff works assuming you properly cross-join your calendar table with your ticket table

--

Sometimes the easiest approach is to reshape your data prior to analysis. Tableau Desktop and Tableau Prep Builder allow you to automatically perform several steps such as Unions, Pivots, Splits automatically - meaning that you don't have to edit your data to have the effect of reshaping and simplifying it.

This answer describes one good way to address your situation. It was written several years ago before Tableau automatically supporting the Union operator, so the answer describes the steps using custom SQL.

Nowadays, you can get the same effect more easily just using Tableau's Union feature.

Here is the more in depth, but just dated, answer

https://stackoverflow.com/a/30200792/441979