4 Replies Latest reply on Mar 25, 2015 11:08 AM by Ramya Anbarasu

# count based on 2 date parameters and one more parameter

Hi guys,

I am trying to find out the count of tickets that were open at the end of each month with respect to severity

This is how I want to view my sheet

The data set that I am dealing with is in the below format

Here is the tricky part, I want to calculate how many defects were open at the end of each month.

For example, the ticket number 234109, was opened on 2-JAN-2014 but was closed in 3-MAY-2014. Which means, it was open by the end of Jan, feb, mar and april. So it should show up while calculating counts for those months under "Low" severity.

Any inputs will be of great help!

• ###### 1. Re: count based on 2 date parameters and one more parameter

Method #2 in  The Cross Join Collection  can be used for this.  You might also consider method #1.

Related

Measure activity between dates

FAQ: Open & Close Dates

• ###### 2. Re: count based on 2 date parameters and one more parameter

Awesome! The cartesian product did the trick! Thank you Kettan. However,

I did a cartesian product between my Defect Table and a new table, that has all the end of the month dates. I performed this query between the 2 tables(Custom SQL Query on Tableau)

SELECT *

FROM [Defect] a, [EndOfTheMonth] b

WHERE a.[Detected on Date] <= b.[EOM] and

(a.[Closing Date] > b.[EOM] OR a.[Closing Date] IS NULL)

The end of the month table is structured like this

This almost gave me the result, but when I tried to build this on a tableau sheet, the EOM from the cartesian product wouldn't show me the drilled down values.

Please refer to this screenshot

I don;t know if I am missing a silly logic, or if this is even doable anymore.

• ###### 3. Re: count based on 2 date parameters and one more parameter

It seems everything in the view is from a secondary data source. It might work if you rebuild the view in a new worksheet so Backlog Cartesian Product (Query... is the primary source.

1 of 1 people found this helpful
• ###### 4. Re: count based on 2 date parameters and one more parameter

Awesome! Problem Solved! I appreciate your help Kettan. Thank you very much