7 Replies Latest reply on Mar 14, 2017 6:06 PM by Nat Vats

    Grand Total and SubTotal Issue

    Nat Vats

      I am having trouble in creating Subtotal for Each Bank and Grand Total for all Banks in the attached sheet. As soon as I include Column Grand Total data starts to appear for all dates. I have attached worksheet.

        • 1. Re: Grand Total and SubTotal Issue
          Jamieson Christian

          Nat,

           

          It looks like your table calc filter is a bit over-engineered. I changed it to just be the following boolean calculation:

           

          Place it on the Filters shelf, compute along Table Down, and filter on "True".

           

          This is the result:

           

          Workbook attached (but it's in version 10.1.5, so you may not be able to open it — sorry).

           

          EDIT: Reposted because I realized I oversimplified the solution and lost marks.

          • 2. Re: Grand Total and SubTotal Issue
            Nat Vats

            Thanks for your response. But I see DOD becomes null and Weekly average and monthly average is same as Deposit where as the data is different for multiple dates.

            • 3. Re: Grand Total and SubTotal Issue
              Jamieson Christian

              Nat,

               

              Yeah, sorry about that. I actually just reposted because I realized I had oversimplified the revised formula. See the edited post and let me know if that does the trick for you.

              • 4. Re: Grand Total and SubTotal Issue
                Nat Vats

                Hi Jamieson- It still does not work as all dates data is coming. Am I doing something wrong? @

                • 5. Re: Grand Total and SubTotal Issue
                  Jamieson Christian

                  Nat,

                   

                  Ugh, sorry. I goofed up again. I shouldn't try to answer questions while multitasking!

                   

                  Actually, I don't know what the fix is for the Subtotal / Grand Total weirdness when table calc filters are in place. I tried rewriting your calculations as FIXED LOD Expressions. Because FIXED LOD's ignore Dimension filters, it would allow you to use a regular filter (rather than a table calc filter) to show just the date the user wants, while still ensuring that calculations that rely on other dates continue to work properly.

                   

                  Here are the calculations that I put together:

                   

                  [DOD]

                  { FIXED [Bank], [Product] :
                  ZN(SUM(IF [Date] = [ReportDate] THEN [Deposits] END)) -
                  ZN(SUM(IF [Date] =
                      { INCLUDE :
                          MAX(IF [Date] < [ReportDate] THEN [Date] END)
                      }
                      THEN [Deposits] END))
                  }
                  

                   

                  [WeeklyAverage]

                  { FIXED [Bank], [Product] : AVG(
                      { INCLUDE [Date] : SUM(
                          IF [Date] > [ReportDate]-7
                          AND [Date] <= [ReportDate]
                          THEN [Deposits]
                          END
                      ) }
                  ) }
                  

                   

                  [MonthlyAverage]

                  { FIXED [Bank], [Product] : AVG(
                      { INCLUDE [Date] : ZN(SUM(
                          IF [Date] >= [ReportDate]-29
                          AND [Date] <= [ReportDate]
                          THEN [Deposits]
                          END
                      )) }
                  ) }
                  

                   

                   

                  The result looks like this. Note that I used a MAX aggregation on the LOD Expressions, because every row will have the same value (and you don't want to SUM them up).

                   

                  WORD OF WARNING: Table calculations have a tendency to "skip over" missing data. In the case of your data, it looks like weekends don't have any data, and consequently, the WeeklyAverage table calc compensated by only stepping 4 data points back (because the data points represent "business days"). LOD Expressions do not function like that, so you have to approach it differently. For DOD, I specifically detected the prior day. For WeeklyAverage, I set the offset to 7 ("calendar days"). For MonthlyAverage, I didn't make any change at all, so check those results carefully.

                   

                  ALSO NOTE: I wasn't sure exactly what you wanted to display in the subtotals and totals for WeeklyAverage and MonthlyAverage. My example workbook (version 10.1.5) makes the assumption that they should be summed (so I set the "Total Using…" to be "SUM" explicitly). If you're looking for a total weighted average, the LOD Expressions may be problematic, because the underlying numerator / denominator that would allow for a weighted average are no longer available after the LOD Expression is evaluated. The best you can do is an "average of averages", which in most cases is not desirable.

                   

                  Workbook attached. It's in version 10.1.5, so you may not be able to open it (sorry). But hopefully the above formulas help.

                  • 6. Re: Grand Total and SubTotal Issue
                    Nat Vats

                    Thanks for the detail explanation. Let me try changing my workbook. Will update you soon.

                    • 7. Re: Grand Total and SubTotal Issue
                      Nat Vats

                      That works. Thanks a lot for your help.