1 Reply Latest reply on Apr 26, 2018 5:58 PM by Shinichiro Murakami

    Nonstandard week-over-week subtotaling

    Kathleen Schroeder

      Hi all,

       

      I am using 10.2 (can't upgrade due to server). My team has a weekly review of metrics, with about a dozen different KPIs. The actual workbook always filters to the two most recent complete weeks, to compare performance. We compare by calculating either a percentage change w/w (for metrics such as "Sales" or "Number of items shipped") or as a basis point delta (for percentages).

       

      The attached workbook is the superstore set made to look like a miniature of what we have. I have created new calcs for the w/w comparisons, and they work just fine. What I need to do is get these into a subtotal row (see attached excel workbook to see what I'm after).

       

      I know about using size() to add custom calcs into the sub/total rows, but using lookup on subtotal rows obviously doesn't work, because that looks up other subtotal rows, and not the data rows for the categories. I've tried to use a fixed LOD on the calc using various date mechanisms, but that gives me aggregation or datatype errors, so I'm at a loss.

       

      I would be eternally grateful to anyone who can help me on this.

       

      Thanks!