10 Replies Latest reply on Dec 14, 2012 9:26 AM by Jonathan Drummey

    Summing accross dimensions

    Kevin Coates

      I'm trying to create a size of giving table in tableau. Tried doing a window sum but couldn’t turn the aggregate into a dimension. In brio I would simply create a calculation that said Sum(Amount,Common ID) and then throw that into a pivot table broken out by fiscal year. Did try a Correlated Sub query and a With clause but both ran endlessly. There has to be a more elegant solution right???

      Attached is some sample data. I want a sum of amount by fiscal year broken at the common id. Any ideas?

      Thanks

        • 1. Re: Summing accross dimensions
          Tracy Rodgers

          Hi Kevin,

           

          I'm a little unsure of what the outcome should be like. I've attached a sample workbook with what I think are a couple of option.

           

          -Tracy

          • 2. Re: Summing accross dimensions
            Kevin Coates

            Thank you Tracey. I added some more detail on the excel file to help clarify my request.

            • 3. Re: Summing accross dimensions
              Ey Tan

              is this what you are looking for?

               

              I've created a calculated field for binning the fields with irregular bin ranges and converted fiscalyear to a dimension.

              • 4. Re: Summing accross dimensions
                Kevin Coates

                Thanks Ey. Not quite what I'm looking for, need the amount to sum accross the common id and fiscal year.

                • 5. Re: Summing accross dimensions
                  Kevin Coates

                  More info.

                   

                  We use this formula to create the size of gift field.

                   

                  IF [NGC_IN_$MM] >= 25000000 THEN "$25MM or More"

                  ELSEIF [NGC_IN_$MM] >= 10000000 THEN "$10MM to $25MM"

                  Elseif [NGC_IN_$MM] >= 5000000 then "$5MM to $10MM"

                  ELSEIF [NGC_IN_$MM] >= 3000000 then "$3MM to $5MM"

                  ELSEIF [NGC_IN_$MM] >= 1000000 then "$1MM to $3MM"

                  ELSEIF [NGC_IN_$MM] >= 500000 THEN "$500K to $1MM"

                  ELSEIF [NGC_IN_$MM] >= 250000 THEN "$250K to $500K"

                  ELSEIF [NGC_IN_$MM] >= 100000 Then "$100K to $250K"

                  ELSEIF [NGC_IN_$MM] >= 25000 THEN "$25K to $100K"

                  ELSE "Gifts Under $25K"

                  END

                   

                  Recently we pre aggragated the totals on the SQL side but lost any dynamic filtering. We want to use a window sum function but when we do the size of gift becomes a measure and we need it to remain a dimension. Any ideas.

                  • 6. Re: Summing accross dimensions
                    Jonathan Drummey

                    Hi Kevin,

                     

                    I'm not clear on what your desired aggregations are. I'm guessing that each Common Id represents a donor, and you want to get the total number of distinct donors for each year and total amount from those donors within each Size of Gift bucket?

                     

                    Also, you write that "We want to use a window sum function but when we do the size of gift becomes a measure and we need it to remain a dimension." but you don't describe what you want to do with that, except perhaps in the spreadsheet. With Tableau, it's helpful to be specific about your final goal because there are often several routes to get there, each with their own tradeoffs.

                     

                    Based on what I was able to interpret, I set something up in the attached. The "special sauce" here is that for the discrete aggregate Size of Gift, I unchecked "Ignore in Table Calculations" in the view. This allows the calc to be used for partitioning and thereby be calculated over for the WINDOW_SUM calcs that generate the # of Donors and Total Amount.

                     

                    Jonathan

                    1 of 1 people found this helpful
                    • 7. Re: Summing accross dimensions
                      Kevin Coates

                      Jonathan, Your interpretation is correct and your edit works wonderfully with the sample data. Never knew about "Ignore in Table Calculations" so thank you. When we try to replicate the process we get overlaying data in the pane like so:

                       

                      Example.png

                      • 8. Re: Summing accross dimensions
                        Jonathan Drummey

                        Given the following layout of pills in the view, the two table calcs should have their Compute Using set to Common Id.

                         

                        screenshot1.gif

                         

                        Also, make sure that the formulas for # of Donors and Total Amount are the same as what I'd sent in the previous post.

                         

                        Let me know if this works for you, if not the next step would be to (possibly helpful) include a screenshot of the view that shows all the pills similar to what I sent, along with the Compute Usings for the table calcs, or (most helpful) a packaged workbook.

                         

                        Jonathan

                        • 9. Re: Summing accross dimensions
                          Kevin Coates

                          Jonathan, this works exactly how I want. Can't thank you enough!!

                           

                          Last thing I need to figure out is how to improve the performance. When I have the common id on the level detail slows the dashboard down to a crawl, of course I have over 100K id's in the data set.

                          • 10. Re: Summing accross dimensions
                            Jonathan Drummey

                            The IF and IIF statements in the calcs I provided are there for optimization, one additional optimization you can do is Ctrl+drag the Total Amount or # of Donors calc to the Filters Shelf, then filter for non-Null values. That will at least reduce the marks that Tableau is trying to draw.

                             

                            The fundamental performance issue with table calcs is that Tableau needs all the data at the given level of detail, so for this view Tableau is pulling # of IDs * # of years rows of results from database at a minimum, so for 100K+ ID's * 5 years that's over 500K rows. That can take awhile to move the quantity of data out of the database and across the wire to your desktop. Extracts can speed this up, but not necessarily as much as needed, you can also aggregate within extracts so aggregating to the year level might be worthwhile. I don't know whether you could aggregate out Common ID and just have the Size of Gift come across in the extract, I haven't tried to go that far with extract optimization.

                             

                            It helps me to think of calculations in Tableau as happening at 3 levels - data row level, aggregation, and table calculation, with N levels of nested table calculations possible. Maximal performance comes when Tableau has to do the minimum calculations possible. So, one thought would be to move the Size of Gift aggregate calculation into your data source (or just aggregate by year & common ID), then the necessary calcs for # of Donors and Total Amount could be regular aggregates and they'd definitely be fast enough.

                             

                            Jonathan