1 Reply Latest reply on Aug 1, 2014 11:50 AM by Shawn Wallwork

    Table calculations and compute using (pane/table across) and hierarchy

    Tor Helge Huse

      I have some CRM campaign data where we have target customers and control customers. Some campaigns are divided in several steps, i.e. the customer first receive an email and then a follow-up phone call some time later. Other campaigns are only one step.

      So I'll have data like this

      CustomerIDCampaignStepNumberOfStepsMediaControl group
      1A12eDMN
      1A22TMN
      1B11DMN
      2A12eDMN
      2A22TMN
      2B11DMN
      3A12eDMY
      4A12eDMY
      5A12eDMN
      5A22TMN
      5B11DMY

      My problem is that in the data control group customers are only recorded with one row (corresponding to the first step), while target customers have one row for each step they have received.

      Now, if I want to compare target vs. control I will get correct counts for number of customers if I take COUNTD(CustomerID) and look at the campaign level, but if I make a hierarchy (Campaign -> Media) the TM step for campaign A will not have any control group customers (as there is none in the data). But a control group customer should of course be a control for all steps. Using the WINDOW_MAX or WINDOW_SUM function and compute using Pane down I'm able to get it correct while expanding the hierarchy to media, but then the numbers for the control group will be wrong when going back up to the top level (Campaign).

       

      Attached is a workbook with the above data and my attempts at using WINDOW_MAX and WINDOW_SUM. With WINDOW_MAX I'm almost there, but when adding one more level to the hierarchy the calculations fail, and the sum on step 2 for campaign A is 0(NULL/missing).

      Is there anyway to accomplish this calculation without altering the raw data (adding the extra rows for the control group customers)?