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

    Calculating the Ratio

    Sandy Wolfkind

      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
          Sandy Wolfkind

          Can someone respond on this please?

          • 2. Re: Calculating the Ratio
            Andrew Watson

            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
              Sandy Wolfkind

              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
                Simon Runc

                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
                  Simon Runc

                  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
                    Sandy Wolfkind

                    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
                      Sandy Wolfkind

                      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
                        Andrew Watson

                        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
                          Sandy Wolfkind

                          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
                            Andrew Watson

                            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
                              Simon Runc

                              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
                                Andrew Watson

                                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
                                  Simon Runc

                                  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?

                                   

                                  Yes you can...I'd advise extracting it to a TDE, you can refresh it at any-time with just a right click (a TDE will be faster, your DB won't constantly be hit, and you get access to all Tableau formulas/function). If you are asking about adding extra tables, columns etc to this Custom SQL join, then you can yes. Right Click on the Datasource and select Edit DataSource and you can add to the custom SQL. Again this is why I would advise creating a view in Vdertica...where you can just update the view, if you need new columns, and then just refresh it and the new columns will be there. In terms of having multiple data-sources, then yes...I was thinking your current structure of data would be easier to do things such as Time from Creation to Resolution where the time, (using a DATEDIFF function) would be much easier across a single Row. I often have several datasources of the same data, just differently shaped...as each is easier to use for certain tasks. I can then bring sheets built from different data-sources onto the same dashboard, and then use Action Filters to filter across them (as of Tableau 10...coming very very very soon!) you can also use Quick Filters across datasources too.

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

                                    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