6 Replies Latest reply on Dec 26, 2012 1:53 PM by Will White

    % Difference Year over Year grand totals

    Will White

      I'm trying to get grand totals for dashboards I have put together. I have already tried zn ifnull and so on. The calculated fields already use zn so there are no values after december of this month (as there should be) but when you use the grand total under analysis it's still calculating the grand total as if the year over year change for jan forward is -100% and including that in the calculation even though when you look at the data there is no -100% there. I just need to exclude certain months from the grand total calculation

       

      I have read all the customizing grand totals blog and still can't seem to make it work. Ive included the wb I was working with. It's a tad messy but the first 24 are all dashboard with the following 50 data. Any help would be greatly appreciated. Thanks a lot

       

      Will

        • 1. Re: % Difference Year over Year grand totals
          Shawn Wallwork

          Will, you'll need to post a packaged workbook (twbx). The one you've posted (twb) doesn't have any data in it.

           

          --Shawn

          • 2. Re: % Difference Year over Year grand totals
            Will White

            Thanks a lot for pointing that out. I have now updated the workbook file....Thanks again for the help

            • 3. Re: % Difference Year over Year grand totals
              Jonathan Drummey

              Hi Will,

               

              What worksheet are you talking about? The Fiscal Year All Data? If so, I don't understand the -100% comment nor the part about the year forward the calcs seem to be working as designed.

               

              How would you like the grand total to be different than it is?

              • 4. Re: % Difference Year over Year grand totals
                Will White

                Jonathan,

                 

                Thanks for the response. Sorry I couldn't state my question more eloquently.

                 

                All the worksheets are giving me a wrong Grand Total. I read your blog posts on the grand totals and can't seem to replicate it well. If you look at the Fiscal Year all data the grand total is including data where there is none. After 96,919 in Nov. under Diff. in receipt amounts, the fields are blank because there is no data but the grand total is including the data there as if the difference is 100% of previous years total. I even setup a null to zeros type of field and got 0's where there are no numbers now and the grand total was still off. Hopefully I explained it better this time thanks again.

                 

                I would like the grand total to only include the numbers you can see in the data sheet. So just totaled from July to November.

                • 5. Re: % Difference Year over Year grand totals
                  Jonathan Drummey

                  Ok, I think I see where the confusion is. From what you wrote, it looks like you want the Grand Total row for FY13 to only do a comparison to July through Nov data from FY12, since there's only July through Nov data for FY13?

                   

                  As you've figured out, that's not how Tableau Grand Totals work, they are a computation at a coarser level of detail, so the Payment Fiscal Period is effectively removed from the Rows Shelf and Tableau is totaling all payments for FY12 and using that total for the  FY13 calcs.

                   

                  It's definitely possible to do this via the Custom SQL technique I wrote about in part 3, I'm not sure whether another method would work, I'd probably go with the Custom SQL technique unless data volumes got really huge because it would be a lot more maintainable, but even then you could do a bunch of aggregations in the SQL to keep volumes down.

                  1 of 1 people found this helpful