
1. Re: Aggregation on Dimension Details within Table Calculations
Shawn Wallwork Aug 23, 2013 12:05 PM (in response to Damian Marquith)1 of 1 people found this helpfulTry:
ADDS
if LOOKUP(attr([customerStatus]),1)="NP"
AND (attr([customerStatus])="PS")
THEN 1 END
DROPS
if LOOKUP(attr([customerStatus]),1)="PS"
AND (attr([customerStatus])="NP")
THEN 1 END
NO CHANGE
if LOOKUP(attr([customerStatus]),1)="NP"
AND (attr([customerStatus])="PS")
THEN 0
elseif LOOKUP(attr([customerStatus]),1)="PS"
AND (attr([customerStatus])="NP")
THEN 0
ELSE 1
END
Shawn

2. Re: Aggregation on Dimension Details within Table Calculations
Damian Marquith Aug 23, 2013 12:59 PM (in response to Shawn Wallwork) 
3. Re: Aggregation on Dimension Details within Table Calculations
Wilson Po Aug 25, 2013 7:00 PM (in response to Damian Marquith)Sounds like you were simply about 1 layer away from getting there.
All that was needed once we saw something like that was to aggregate the counts 1 step further to get the total number of ADDs, DROPs, and NO CHANGES. By using a cohort calc within a WINDOW_SUM, we can figure out the # of customer for each period based on that criteria:
WINDOW_SUM(IF [STATUS CHANGE]="ADD" THEN 1 END)
We repeat this for each status change and address this new calc based on the CustomerName Level. This allows for Status to still be evaluated based on the Fiscal Month, but then we add up the customers within the period based on the criteria laid out for each formula. Finally, turning on the "Stacked Marks" will cut down the noise with each customer repeating the total value, and instead layer the totals for each customer in the same area.
While we could take this one step further and limit the returns for totals to avoid overlaps, its probably not worth the extra effort. Any time you are partitioning across a table calculation (as oppose to a dimension found in the data), it will get somewhat difficult and intensive.
Hope this helps!

LOOKUPTEST.twbx.zip 67.2 KB


4. Re: Aggregation on Dimension Details within Table Calculations
Damian Marquith Sep 5, 2013 2:24 PM (in response to Wilson Po)Perfect!! Thank you very much.