7 Replies Latest reply on Apr 5, 2012 10:50 AM by Jonathan Drummey

    Fixed Format Reports - Calculation

    Ahmed Ahmed

      Hi All,

       

      I have just started working on Tableau and we are invited by the prospect to do PoC. If we can successfully deliver then  this will the first customer in the region. The problem is PoC requirements are really difficult for me. Prospect wants us to build Balance Sheet & Income Statement in Tableau. On top of that, We have to create lot of extra headings in both the reports e.g. Current Assets, Fixed Assets etc.

       

      Another requirement is to perform the calculation on these added headings. To make my case strong, following is the example:

       

      Assets                       6,700 (Added Heading, Calculation Current Assets + Fixed Assets)

      Current Assets           2,200 (Added Heading, Calculation Cash + Receivables)

      Cash                         1,000

      Receivables               1,200

      Fixed Assets              4,500 (Added Heading, Calculation Plant + Machinery)

      Plant                         2,400

      Machinery                 2,100

       

      The data source is Baan ERP with data base in sql server. Report requirement is straight table no hierarchies.

      I hope the requirement is clear.

       

      Any ideas how to approach this issue.

       

      Kind Regards,

       

      Ahmed

        • 1. Re: Fixed Format Reports - Calculation
          Jonathan Drummey

          Hi Ahmed,

           

          If you haven't already, check out the on-demand training at http://www.tableausoftware.com/learn/training. There are a ton of videos there to walk you through learning Tableau. If you like the written word, the Freakalytics book Rapid Graphs with Tableau is also really useful, see http://www.freakalytics.com/.

           

          It seems like the report is a basic crosstab. I've attached a sample workbook. In Tableau, you can easily create calculated fields that add the measures or aggregations of measures together. Then you'll put Measure Names on the Columns shelf and Measure Values on the rows shelf, and select the measures you need, and then do some formatting to get things to display the way you want.

           

          I'm assuming that your datasource already has the initial measures ready for you, if it doesn't then that's a different set of questions.

           

          Jonathan

          • 2. Re: Fixed Format Reports - Calculation
            Ahmed Ahmed

            Hi Jonathan,

             

            As I mentioned in the post that its a straight table and in the example, the Assets, Current Assets & Fixed Assets are the few headings that I have to include in my report. These headings and the values are not available in the source. I have to introduce these headings and have also perform the vertical summations.

             

            First milestone is to add the missing headings in the straight table and second one is to calculate the vertical summation of data based on the heading e.g. to calculate the Fixed Assets, I have sum the value of Cash & Receivables.

             

            I did check all the videos but thanx for the book.

             

            I hope that understand the issue well.

             

            Ahmed.

            • 3. Re: Fixed Format Reports - Calculation
              Jonathan Drummey

              I'm sorry, I don't think I'm understanding exactly what you want. Are you needing the headings to be text or the values of the measures (or summations of measures)? If you created a mock-up document or spreadsheet, I'm pretty sure something could be figured out.

               

              FYI, Assets, Current Assets, and Fixed Assets measure values can be calculated fields and don't require the measures they are based on to be in the view. You can see this in the worksheet I provided, if you drag one of the fields off of Measure Values the rest of the calculated fields stay the same. By default, Tableau makes the heading name the same as the name of the measure, however when using Measure Names/Measure Values you can override that by right-clicking on the header label and selecting Edit Alias...

               

              Jonathan

              • 4. Re: Fixed Format Reports - Calculation
                Ahmed Ahmed

                Hi,

                 

                Seems, probably I am not able make others understand the issue. Let me try one more time.

                 

                The data source doesn't know what Asssts, Fixed Assets & Current Assets are. These terms are aliens terms for the prospect's data source. My task is two fold:

                1) Introduce the above mentioned terms in the report

                2) To calculate the values against each terminology.

                 

                I am sorry, dont have a mock sheet.

                 

                Ahmed.

                • 5. Re: Fixed Format Reports - Calculation
                  Ahmed Ahmed

                  Hi,

                   

                  I have attached the excel sheet here. The understanding is the Code that starts with 101 are Current Assets whereas that starts with 102 are Fixed Assets.

                   

                  We are not allowed to add an external sheet or table that will have the information about what Assets, Current Assets & Fixed Assets are. The only thing that we know is Code values with prefix 101 are Current Assets and prefix 102 are Fixed Assets.

                   

                  Based on this information, I have to show on the report Current Assets, Fixed Assets and Assets.

                   

                  I hope this will make the case clear.

                   

                  Ahmed

                  • 6. Re: Fixed Format Reports - Calculation
                    Jonathan Drummey

                    Hi Ahmed,

                     

                    Since you've listed the order of operations twice now as 1. "create the header" and 2. "calculate the values", I think you might not be clear about how Tableau works, which is kind of the opposite.

                     

                    Tableau starts with a dataset, which it flattens into a tabular format with columns being dimensions and measures, and rows being values. Tableau also allows you to create new dimensions and measures on the fly by creating calculated fields. Then you drag your dimensions and measures (whether original or calculated) into the view and Tableau automatically generates header and data columns and rows based on the combination of those.

                     

                    I assume that your dataset has columns like Cash, Receivables, Plant, and Machinery. Therefore, you can create new calculated fields like "Current Assets" that would have as it's function [Cash]+[Receivables]. I did that in the workbook I attached above for the "Margin + Profit" measure, which is [Margin]+[Profit]. Calculated fields can be based on other calculated fields, so "Assets" could be [Fixed Assets] + [Current Assets].

                     

                    When you want to show multiple measures at once in a crosstab, you can drag Measure Names (a convenience variable) to the Columns shelf, then put Measure Values (another convenience variable) on the Text shelf. This creates a new Measure Values card that lists all the measures that will be displayed on it, and you can drag and drop those measures off it. As you do so, the fields will be removed from the view, but the calculations will not change.

                     

                    Does that make sense? And, more importantly, does that meet your needs?

                     

                    Jonathan

                     

                    --

                     

                    You were adding the sample data when I as making this post, and now I understand your comment about needing to make headers, I'm working on an update.

                     

                    Message was edited by: Jonathan Drummey

                    • 7. Re: Fixed Format Reports - Calculation
                      Jonathan Drummey

                      Hi Ahmed,

                       

                      Here's an updated version. Seeing your data helped make the issues you were experiencing make sense. The data you supplied is "tall" and we need to make it "wide" by adding not just the headers for the Assets fields, but also the other columns. Tableau has a special way of generating crosstabs that isn't as easy as just dragging a dimension like the Description from the Master table and then tacking on a few more.

                       

                      Therefore, I created a calculated field for each column in the desired output. To generate the Fixed and Current Assets columns, I did a string comparison, here's the one for Current Assets: IF LEFT(STR([Code]),3) = "101" THEN [Amt] END. The Assets column needed to be SUM([Current Assets])+SUM([Fixed Assets]), I'm not sure why Tableau didn't want to just add Current & Fixed together, apparently Tableau is a bit limited in how much it nests adding row-level calculations as opposed to aggregate calculations (which I do know can have many levels of nesting).

                       

                      Let me know if this works for you,

                       

                      Jonathan

                       

                      PS: If you needed more than a dozen or so headers, then I'd probably approach this differently and use Custom SQL to do a UNION of a self-join(s) on the data, with the original data returned as one query and then additional queries to return the necessary aggregates as additional rows. This would be more efficient in Tableau than creating too many calculated fields.