6 Replies Latest reply on Mar 13, 2013 8:51 AM by David Mendel

# Calculating on two date fields

I'm trying to calculate a ratio based on two date fields.

Specifically, I want to calculate the number of bugs created for a given product over the number of bugs fixed for that product in the same month.

I have four columns that need to figure into the calculation Bug ID, Product, Create Date & Closed Date. So for instance if 5 Bug IDs were associated with Product XYZ and had a Create Date in March, and 4 Bug IDs were associated with Product XYZ and had a Close Date in March, I would expect to see a ratio of 1.25 for XYZ in March.

• ###### 1. Re: Calculating on two date fields

David,

I've posted a workbook that gives a possible solution using table calculations.  It's quite complex, but I've run out of time to write up any documentation tonight.  I'll try to give more explanation as to how it works tomorrow -- if for some reason I get distracted feel free to post a response to this thread to get my attention, or email me (my address is on my bio page).  I'd be happy to answer any questions you might have!

Regards,

Joshua

• ###### 2. Re: Calculating on two date fields

Hi,

A solution attached. It requires duplicating the datasource and joining them together. I've created a custom date in each connection for this - the Month/Year of the created date and closed date respectively.

By joining on these dates we create a bit of a date dimension, common to both datasets, and show the numbers closed/open base on that date. You can then add the ratio calculation as included.

This works because the created date is reliably a longer timeframe that the closed one. In other datasets this isn't the case and you really need a third date dimension to hang the data from.

The sheet data table - blended shows the working numbers.

1 of 1 people found this helpful
• ###### 3. Re: Calculating on two date fields

Cool, I knew there was a table calc way.. I'm having a dig through this

• ###### 4. Re: Calculating on two date fields

Nathaniel,

I just updated the workbook as there was a slight error (I should know by now to check my work before posting! )

Please get it again if needed.

Regards,

Joshua

• ###### 5. Re: Calculating on two date fields

Thanks Joshua. It certainly looks correct. I need to spend sometime digging into it to understand what you've done.

• ###### 6. Re: Calculating on two date fields

Thanks Nathanial, this solution appears to work too, and might be easier as the data is actually in a database and rather that duplicating the dataset in Tableau, I could just take care of it with a SQL join.