1 2 Previous Next 17 Replies Latest reply on Jul 29, 2016 9:59 PM by Ashish Chaudhari

# Calculating the Ratio

Hi

Can someone help me in trying to figure out solution for the following issues please..Attached is my application

1. I am trying to calculate the Ratio between Created Bugs and Resolved Bugs , say for example July 2016

Created = 2144

Resolved = 2031

The ratio is 1.05 ,but I don't get the correct ratio displayed when included by Priority ,

I want to see Ratio by Priority something like below

2. I have duplicated the data model to join on Resolution Date to create the Created vs Resolved Chart , but the numbers are slightly Off , how do I avoid this ?

Example : Resolved Bugs for July 2016 - 2033 , when joined I get 2031

3.How do I apply filters when I duplicate the data models , i need to have Filter on Mon and Year , if i take Created Date for the filter then it filters based on created date for resolved Date Table which would give me wrong numbers .. Is there a way i could filter on One date and that date is considered for Resolved and Created separately?

Thank you !

• ###### 1. Re: Calculating the Ratio

Can someone respond on this please?

• ###### 2. Re: Calculating the Ratio

There are a number of things going on here. First recommendation is to restructure your data so removing the necessity to blend. Union your data aligning the dates (this is causing you a problem), i.e. for created bring in Jira Create Ts, Priority, a flag indicating it's a Create (a C perhaps) and union it with the resolved, bringing in the Resolution Jira Event Date, Priority and a flag indicating it's a Resolution. This would make things so much simpler if you can do that.

Your more specific questions...

1. Bring the data into 1 source and this is simple, blending perhaps not possible. You're trying to compare the priority of those created in a given month with the priority of those resolved in a given month. The creation and resolution are two distinct events which are related but aren't joinable. This is too difficult for me to explain in writing but doing the union will solve the problem.

2. Interesting question. I suggest viewing the data of the 2033 and the 2031 and seeing which 2 are missing, then work out why. Sorry, I don't have the time to do that but am interested to hear the result.

3. Union the data, only solution for this one

2 of 2 people found this helpful
• ###### 3. Re: Calculating the Ratio

Really appreciate the response..

This is my first Tableau application , can you please help me with doing the Union of the data

Thanks!

• ###### 4. Re: Calculating the Ratio

hi Sandy,

So, I think, the issue here is that you have blended the [Create Date] to the [Resolution Date] at the exact level...so the reason you are getting 2031 and not 2033 is there must be 2 days, in July 2016, where there is no Resolution for a Day, or no Create for a Day (or a few days). This means the Blend has nothing to link those records together...

To get round this I've changed the Data Source Relationships slightly....you'll notice above a date, you have a little expander/drown-down. This lets you just blend at that level. As you want the data at Month level I've selected to Blend on Month. This means it's just looking for where Create-Date = July 2016 and Resolution-Date = July 2016...rather than looking for Create-Date = 1st July 2016 and Resolution-Date = 1st July 2016...etc.

The image below shows this, and the resulting chart which now shows the 2033 figure.

Hopefully that will sort out your ratios being slightly out too.

1 of 1 people found this helpful
• ###### 5. Re: Calculating the Ratio

hi Sandy, Having just seen Andrew's response...I would agree with him on that. It will make your Tableau life much easier. Blending isn't quite a left join...it's actually an aggregate (at the Blended Fields level) and then left join on the Blend-field levels.

I've written a blog on exactly this subject...The Importance of Granularity (…to Blend or not to Blend?) | The Data Animators

As such all fields you bring in from your secondary source come in aggregated (eg. with a ATTR, SUM, AVG...) and you are also unable to do certain things with COUNTD. This can create problems when you want to do certain things.

However..as it seems you have enough activity (at the month level) that you will always have some created jobs and some resolved jobs each month, you might be able to get away with this!!

Like Andrew I won't have time today, but if you do want to explore the UNION option can you let us know what source your data is from (Excel, SQL...etc.), and if in SQL (or other database) are you able to create a view to feed your Tableau model?...it'll just help us know if we need to use custom SQL option (and if we have to deal with Excel's Jet SQL driver!!)

1 of 1 people found this helpful
• ###### 6. Re: Calculating the Ratio

Thank you very much , this makes sense !

This fixed the overall count but when I look into the Ratio , why is my individual ratio by priority defers?

Say for example :

July 2016 P0 - 266 Created

July 2016 P0 - 174 Resolved

Expected Ratio : 1.52

Ratio shown is 0.130

Any thoughts on this?

• ###### 7. Re: Calculating the Ratio

Thank you Simon , I agree with both of you .. with the similar requirement the first thing which comes to my mind when dealing with any other reporting tool which I have been working with, Union is the first option comes to my mind.

But was not sure how to do it in Tableau , as there is No script window kind of to write the script , my source is Vertica , regular sql should work though ..

• ###### 8. Re: Calculating the Ratio

As Simon says your data source dictactes the union to a large degree. It appears to be Vertica so you should be able to write SQL against this.

This *might* work for you and should at least serve as a base:

SELECT [Jira Create Ts] AS EventDate

, Priority

, 'C' AS EventType

FROM YourTableName

UNION

SELECT [Resolution Jira Event Date]

, Priority

,'R'

FROM YourTableName

1 of 1 people found this helpful
• ###### 9. Re: Calculating the Ratio

Sorry for my very basic questions , I do see that there is Initial Sql in the Data menu .. that's where the sql goes right ?

and also can i have this sql as one of the Data source ? I mean say for example i will have to include more tables/sql's in the future in the same application .. can i do that?

Thanks!

• ###### 10. Re: Calculating the Ratio

If you right click on one of the ratios you are able to 'View Data'. You can then look at all of the underlying data and work out what is happening.

• ###### 11. Re: Calculating the Ratio

hi Sandy,

I've not connected to Vertica before, so am assuming it has the same options as any other DB I've connected to. You can either write a view (with the UNION) in Vertica and then connect Tableau to that view...or you can use the Custom SQL

rather than dragging a table into the view, drag the New Custom SQL instead...that will bring you up a text box where you can write the SQL statement.

If you use the UNION it will be easier to get to the ratios...but for interest (and you may start to see why this blending-hack will cause problems, which can be over-come...but you'll always be fighting!!) the following arrangement get you what you expect (I no longer have T9.2, so am unable to post you a workbook back)

As you can see I have added Priority to the Blend...as it's an aggregate and join, and the only Blend field was date...it brought back the same value for each Priority for a Month (as it didn't know it needed to do a further cut by Priority too). I'm also using the Resolution Date as the Filter and Month Display...

• ###### 12. Re: Calculating the Ratio

You can have the SQL as a datasource. I've never actually connected to Vertica before but I would expect it would go where it says Initial SQL.

If it doens't work hopefully you can use the Custom SQL option Simon mentions.

• ###### 13. Re: Calculating the Ratio

Initial Sql in the Data menu .. that's where the sql goes right ?

...I think you could, but this is really meant for initial pass-through stuff (like permissions, if they rely on a query...not my area of expertise, I have a DBA to worry about that stuff!!, but that's my understanding of the Initial SQL)...if you click through to connect as usual you should see the Custom SQL option, which is where the UNION should go.

also can i have this sql as one of the Data source ? I mean say for example i will have to include more tables/sql's in the future in the same application .. can i do that?

1 of 1 people found this helpful
• ###### 14. Re: Calculating the Ratio

One point on creating an extract (TDE) - I believe, but am happy to stand corrected, Vertica is a column store DB so there won't be any performance improvement to have an extract, in fact better to connect live so the data is always up to date.

Fully agree having multiple cuts of the same data is a big help depending on what is being shown within the report.

Sandy, I suggest you brush up on SQL :-)

2 of 2 people found this helpful
1 2 Previous Next