2 Replies Latest reply on Jul 9, 2013 10:23 AM by matthew tagliaferri

    Total Columns by Pane

    matthew tagliaferri

      Newbie to table calcs and such, so having trouble with this one

       

      My data goes across by month, showing last year and this year side by side.

       

      2012  2013       2012  2013    2012  2013    

      April  April        May   May    Total   Total   

       

      I need to create the total columns for each pane, as shown above.  Seems like some type of running total table calc maybe, but I haven't worked enough with them.

       

      Screenshot of my Viz-in-progress attached.

        • 1. Re: Total Columns by Pane
          Jim Wahl

          Hi Matthew,

           

          Since you want totals at the yearly aggregation and your date dimensions are organized by Month > Year, you need to go outside the default functionality, which will only give you subtotals at the month or grand totals at the month+year level. I'm sure you knew this already, just repeating to clarify for myself.

           

          There are a couple of approaches. The first and easiest is to just duplicate the worksheet, remove month from the columns shelf on the second sheet. This should give you your yearly totals. Now you can combine these two sheets in a dashboard. The main problem with this is that you can't select or expand hierarchies (you can do it on the main sheet, but it won't be reflected in the second sheet).

           

          The other approach is to use custom SQL to duplicate the data set with a UNION ALL, adding a "source" identifier on each row to indicate data or totals. This enables you to create a custom month calc that for "data" rows returns the month and for "total" rows, returns "total"---both are then partitioned by year on the columns shelf.

           

          The attached workbook has examples of both and more detailed instructions.

           

          You should also check out Jonathan Drummey's excellent presentation here on totals and sub-totals: TDT: Grand Totals & Subtotals with Jonathan Drummey - May 9th @ 9AM PT.

           

          Jim

          1 of 1 people found this helpful
          • 2. Re: Total Columns by Pane
            matthew tagliaferri

            this will work great for me, thanks much.