13 Replies Latest reply on Dec 5, 2016 8:25 AM by vincent chiew

    How can I prevent ratios from consolidating on quarters and years?

    Joel Landry

      I am new to Tableau and we are currently evaluating it to see how viable it is as a reporting layer on top of TM1 to produce dashboards. We are testing with version 9.2 of Tableau. My team manages all of the TM1 applications internally, so we can make changes in TM1 if it proves to be easier to support this effort. We're currently running a process in TM1 to flatten the data and import it as a Text file to Tableau. We're aware of TM1Connect and will look into this further if the tool looks like something we can leverage.

       

      Since we support Finance, a large amount of our metrics are ratios. The ratios can not aggregate for quarters or years. They have a separate calculation for consolidated time periods. The calculations for these ratios on quarters and full years can be very complex. Since these are all built in TM1, I'm trying to avoid having to re-build them in Tableau. I was able to get around this be importing the TM1 time dimension as a string.  An example in TM1 would be 2015 Jan, 2015 Feb, 2015 Mar, 2015 Q1.  We only look at months, not days.  However, I can split this or convert it to any format coming out of TM1 if it helps.

       

      This solution works to keep the ratios on the quarters and years calculating correctly.  However, since we have no "Time" dimension, we seem to be limited on the charts that we can choose as some require at least 1 Time dimension.  Any suggestions on how I can keep my ratios coming from TM1, but still create a Time dimension to get the benefits of the additional charts?

       

      Thank you

        • 1. Re: How can I prevent ratios from consolidating on quarters and years?
          Nicholas Hara

          Hi Joel, have you seen this article?

           

          Correcting Date Fields that Tableau Interprets as Strings | Tableau Software

           

          Basically, convert the field into something Tableau recognizes as a date and then perform analysis from there.

          • 2. Re: How can I prevent ratios from consolidating on quarters and years?
            Joel Landry

            Thanks for the quick reply Nicholas.  Yes, I had found similar articles on converting the date and was able to do that successfully.  Unfortunately, this does not resolve the issue on ratios summing in quarters and\or years.  This solution works great for dollars, but I need a way to handle both ratios and dollars.  I'm hoping there's a way to use the time dimension type, have quarters\years in the hierarchy, but have the tool leverage the data file to capture those amounts (as they're already in the TM1 data file) rather than summing to calculate them.

            • 3. Re: How can I prevent ratios from consolidating on quarters and years?
              Nicholas Hara

              Hi Joel, sorry to hear that the article didn't resolve the issue.

               

              Do you happen to have a sample workbook that we can look at?

               

              I don't think I'm quite getting the issue from the description.

               

              Additionally, if you have the desired result vs the actual result that will help us understand what is occurring as well.

               

              Thanks.

              • 4. Re: How can I prevent ratios from consolidating on quarters and years?
                Joel Landry

                Ratios.PNG

                Forgive me as I’m still new so I’m unfamiliar with saving a workbook that you’ll be able to open and leverage the data source. Also, I was having difficulty parsing up the string date on the quarters and the full year. I did what I hope will be the next best thing. I put together a simple example in an image of what I want it to be and what I’m getting instead.  I made up some data and a simple calculation.  I’m hoping it may be easier to follow this way.

                 

                In this example we’re calculating “Incurred Loss Expense Ratio” which is “Incurred Loss Expenses” / “Core Earned Premium”.  This is currently handled in TM1 which will be my data source.  I know we can replicate the calculation in Tableau, but this is a very simple example of a ratio.  We have many more, many of which are rather complex.  I’m hoping we can leverage what we already have built and don’t need to recreate the wheel to display the data.  I want to be able to use more of the charts, some of which seem to require at least one date dimension.  If I bring in the date as a
                string, I get the numbers to work, but I’m limited on the chart selection. We have no issues on the dollars, (Core Earned Premiums or Incurred Loss Expenses), just the ratios.

                 

                In this example, I identified the numbers as we should see them in the top example.  I then show what happens if we just bring in the months and let Tableau create the Quarter and Full Year for 2015.  As you can see, we get very different results as the quarters and year are summing.  What we need is to extract the data from TM1 for months, quarters, and years but have a way to leverage the date dimension in Tableau so we can use other charts (like a continuous line chart). 

                 

                Please let me know if this example helps, or if you need more detail.

                 

                Thanks again

                • 5. Re: How can I prevent ratios from consolidating on quarters and years?
                  Nicholas Hara

                  Hi Joel,

                   

                  While I appreciate the example, I'm still not sure if what I'm looking at is your result or if it is your actual data.

                   

                  I'm just not sure what this looks like in Tableau right now. You can attach a sample workbook using the advanced editor button

                  advancedEditor.png

                   

                  attach file.png

                  You can package a workbook for us to look at by going to File>Export Packaged Workbook. This will store the data in with the file and allow us to take a peek at what's happening.

                   

                  My main question, is this what the underlying data looks like or is this the desired result?

                  • 6. Re: How can I prevent ratios from consolidating on quarters and years?
                    Joel Landry

                    Ok here are 2 different versions of the workbook. In one instance I pulled in all time periods and left it as a string dimension.  This produces the correct results for the quarters and full year 2015, but since I don't have a Date dimension assigned I can't use all of the graphs.  In the other version (IncorrectRatios) I pulled in just the months so I could have a Date dimension to see the graphs, but the consolidated quarters are pulling the summed amounts.  How can I have a date dimension but pull the quarterly or yearly amounts from the extract file?

                    • 7. Re: How can I prevent ratios from consolidating on quarters and years?
                      Nicholas Hara

                      Hi Joel,

                       

                      There is a lot going on in your workbooks, and it looks like most of the data has been pre-aggregated. This isn't bad, it simply means that we have to give Tableau a little more direction about how certain numbers are calculated and be sure to illustrate how different measures are related to each other.

                      1. Data sets where there are multiple levels of aggregation in the same measure are confusing to Tableau. Your second workbook with incorrect results is much more conducive to working with Tableau. As you can see on sheet 3 of the revised workbook, after converting to a date, there are 4 nulls. these are your 2015Qx entries. Tableau does that aggregation for you, so these nulls aren't a big deal. just filter them out.

                      2. Instead of SUM(Ratio) we are actually looking for the AVG(Ratio). To be even more precise we are looking for the Weighted AVG based on the number of units per month. I built a simple view using Subtotals to show how this effect can be accomplished. Subtotals

                      3. The ratio measure, since it is hard coded into the database, ignores the weight of the total for the month. We cannot simply use the average and receive the correct answer. Instead, we have to rebuild the ratio calculation in Tableau.

                      a. Go to Analysis> Create Calculated field

                      b. Enter the formula: SUM([Incurred Loss Expenses]) / SUM([Core Earned Premium])

                       

                      Hope this all helps!

                      • 8. Re: How can I prevent ratios from consolidating on quarters and years?
                        Joel Landry

                        Thanks for putting this together Nicolas. I'm aware that the calculation can be built in Tableau, but that's exactly what I'm trying to avoid. The example I gave you was one of our simplest calculations. Most of them are much more complex and we have many more.  I'm trying to avoid having to recreate all of these. Not only would this be a duplication of efforts upfront, it's also more maintenance if a calculation gets changed as we'd need to complete it in two systems and ensure that they are kept in sync. What I was hoping was that there was a way to turn off the consolidation on the Date in Tableau since the math is already done in the data source tab. 

                        • 9. Re: How can I prevent ratios from consolidating on quarters and years?
                          Joel Landry

                          Also, if we did decide to build all of the ratios in Tableau and we have multiple users that will be building workbooks leveraging this data or similar data with ratios, would they each need to replicate the logic independently?  The benefit of leveraging the ratios in TM1 and pushing them to Tableau is that they are all built, stored, and maintained in one place. We wouldn't have to rebuild the wheel each time we use a leverage a new ratio, or an existing one. Is there an easier way to think about this?

                          • 10. Re: How can I prevent ratios from consolidating on quarters and years?
                            Nicholas Hara

                            Hi Joel,

                             

                            I understand completely the desire to keep things simple.

                             

                            The core issue is that you are turning an OLAP Cube (TM1) into a flat file. If you aren't familiar with the differences there is a very helpful article here: Understanding Functional Differences Between OLAP and Relational Data Source Connections | Tableau Software

                             

                            When Tableau reads your CSV, it assumes that all of the rows correspond to the same level of detail and that some rows are not aggregations of the preceding rows. Except, in your situation, they are. Since there is not a native connector for TM1, you are a bit stuck with the CSV as your data source in Tableau. This presents a particular headache and there are many ways to approach your particular problem. I can give a general overview of some possible options, but at this point, I would highly recommend seeking out a consultant that can really dig into the solution that best fits your needs.

                             

                            Option 1. Rebuild the calculations in Tableau as we discussed. There are a few ways to go here.

                            a. Ditch the calculations in TM1 and rebuild in Tableau Desktop

                            b. Keep two copies of the calculations in Tableau and TM1

                            c. Leverage Tableau Server to build the calculations in Tableau Desktop, and then serve the published data source to all your users.

                             

                            Option 2. Build 'exception' calculations for your different levels of aggregations. This can get complex and messy quite quickly, but the general thrust is that for every measure you would create a 'Quarter' calculation and a 'Month' calculation. Something along the lines of: IF CONTAINS([Date],"Q") THEN [Ratio] END. That would be your Quarter Calculation. Your month calculation would remain the same as the date calc that you built before.

                             

                            Option 3. Find a way to the relational database back-end. I'm not familiar at all with TM1, but most Cubes are based on a traditional relational database model. It may be (and that's a big maybe) possible to get a direct connection to that data, which could significantly reduce your headache.

                             

                            Option 4. Deploy a traditional Database such as My

                            • 11. Re: How can I prevent ratios from consolidating on quarters and years?
                              Nicholas Hara

                              Sorry, accidentally hit send!

                               

                              Option 4. Deploy a traditional Database such as MySQL or IBM DB2 and prep the data before analyzing it in Tableau

                               

                              Option 5. Use an Extract Transform Load or Data Prep tool such as Alteryx to get the data into a more readily useable form.

                               

                              Hope these options help out!

                              • 12. Re: How can I prevent ratios from consolidating on quarters and years?
                                Joel Landry

                                Thanks Nicolas.  I’ll keep exploring as well.  We may look at a tool like TM1Connect if we decide to move forward with Tableau. Again, thanks for your time and effort.

                                • 13. Re: How can I prevent ratios from consolidating on quarters and years?
                                  vincent chiew

                                  Hi Joel,

                                   

                                  Just wondering if you managed to get a solution to this issue? We have just started to use Tableau to visualise TM1 data and have similar dilemmas in reproducing TM1 calculations outside of TM1.