4 Replies Latest reply on Oct 8, 2013 12:03 PM by dave.whent

    Are calculations on discrete records possible?

    Ashley Sink

      I'm a new Tableau user and am working to highgrade an Excel model for use and manipulation in Tableau. The model uses current actual data and a set of assumptions based on historic data to project future data at the same level of detail as the current data. The model in Excel calculates everything based on discrete records. There is a calculation for each record for each year including current year (actual data) and 30 future years (projected, calculated data).

       

      In Tableau, I understand how to do calculations based on aggregate data over the 30 future years. But I'm unable to do calcuations on discrete records. My data is only meaningful when the discrete records are calculated then aggregated.

       

      Anyone know of a way to do calculations on discrete records in Tableau (and then I can aggregate those for analysis)?

        • 1. Re: Are calculations on discrete records possible?
          Jonathan Drummey

          In Tableau, you can do calculations on discrete records and use them in aggregations. You'd first create a calculated field that manipulates the data in some way (simplest thing I can think of is a calc like -[Sales] to return the negative amount of [Sales], and then if the default Tableau aggregations Sum/Count/etc. don't work then you can create your own calculated fields. Beyond that, there are table calculations for further aggregation.

           

          In other words, you can create calculations at any of the following levels (and combine them together into a single calculated field, with some limits):

           

          [Sales] - acts on discrete records

          SUM/COUNT/etc.([Sales]) - aggregation determined by what discrete (blue pills) are on Columns and Rows shelves, and what's on the shelves on the Marks card

          WINDOW_SUM/etc.(SUM/COUNT/etc.([Sales])) - table calc aggregation determined by what's in the view and what you specify for addresssing and partitioning

           

          The "gotcha" with Tableau is that when mixing calculations on discrete records and then aggregating those results in the same view, you need to make sure that you aren't returning multiple values for each displayed mark. For example, if I have an IF/THEN statement like IF [Sales] < 100 THEN "Under $100" ELSE "Over $100" END and then I put that on the Color shelf in a view that aggregates by Product Category, I'll get overlapping colored marks. So then you need to do one or more things to make the calculation work, the simplest is to use SUM([Sales]) for the comparison. I've attached a workbook showing this.

           

          Jonathan

          • 2. Re: Are calculations on discrete records possible?
            Ken Patton

            Also, it may be necessary to invoke Analysis -> Do Not Aggregate Measures in the menu , so that the calculations can be performed and then the results aggregated. But try the default setting first.

            • 3. Re: Are calculations on discrete records possible?
              dave.whent

              Just wanted to pop in and say this thread was really helpful.  Thanks.

              • 4. Re: Are calculations on discrete records possible?
                dave.whent

                Just wanted to pop in and say this thread was really helpful.  Thanks. x