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

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

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)?

• ###### 1. Re: Table calculations and compute using (pane/table across) and hierarchy

Tor, what you describe is one of the limitations of using Table Calculation, they can't (to the best of my knowledge at this time) be addressed on fly as you would need when using a hierarchy. An alternative is to setup two sheets in a dashboard container and use a parameter-driven show/hide filter so when one level is chosen the other blanks and is then hidden.

Others will have other workarounds.

--Shawn