Trouble getting consistent results on a table calc with different views.
Justin Larson Feb 9, 2013 12:57 PMWhat I have here is a table calculation computed along one field. it works fine when I reorganize the pills and layout, but it breaks when I add dimension pills to the view. What I think I'm trying to figure out is how I can structure the formula to respond only to filters. I want to be able to add any additional dimensions to the view without changing the outcome of the formula. In specific, this formula is being used to color a chart, and I want to be able to see the same colors used to color the dots of customers on a map.
The specifics are particularly hard to describe, but I'll do my best, in conjunction with a simplified sample workbook I've whipped up to demonstrate. This is a segmentation projection in which we are using this workbook to target and rank customer segments. The idea is that the segments are identified as highly important or notsomuch by comparing aggregations of customers.
So, goal here: color the dots on the map by calculated field "Segment Strength" matching the outcome from the same calculation on the profile chart.
1) Data description
Union query
part one of the query is every possible customer.
Each line is a customer with an ID; segment is an attribute of the customer and each line is marked as "Base"
part two of the query is what we'll call "orders",
Each line is an order, which is marked "target"
this part includes customers (with their ID), plus a product they ordered and a volumetric field used for aggregation
2) How the calculations look
Compare each segment's percent of total in base to each segment's percent of total in target. (this comparison is the target index0
For target, the metric used for percent of total calculation is the Volumetric field. For base, it's 1 per record.
3) Point of the exercise
each segment is evaluated using the "Segment Strength" calculation, which relies on Target percent of profile, and target index.
this Segment Strength should be evaluated the same, no matter what is in the view (as long as the minimum level of detail is in the view, obviously)
The profile tab shows this formula successfully calculating. I'm trying to get the same results on the map, or on a chart that contains more pills than just segment.
Please comment and ask questions as necessary. It's difficult for me to know how much context is too much or not enough for this to make sense to an outsider. My live workbook contains substantially more data, and the idea is that I can apply any filter to the target to do a custom profile of, for example, "customers who bought product1," just as easily as "Customers who live in NYC," etc.