
1. Re: Noob on this calc..Dimension subgroups..??
Tracy Rodgers Feb 28, 2012 1:58 PM (in response to Kevin Andrist)Hi Kevin,
I have attached a sample workbook with the desired view using the data provided. First, a calculation needs to be created for each Model and set it to Units.
Ex: if [Model]='Baseline' then [Units] end
if [Model]='Scen1A' then Units end
Then, a third calculation should be created which subtracts these from each other.
sum([Baseline Units])  sum([Scen1A Units])
Notice, on Sheet 2, Model is no longer on the view. Rather, Measure Names takes it place.
Hope this helps!
Tracy

variances example.twbx.zip 5.8 MB


2. Re: Noob on this calc..Dimension subgroups..??
Kevin Andrist Feb 28, 2012 2:05 PM (in response to Tracy Rodgers)Thanks Tracy..I'll give it a look and will followup if there are questions.
I assumed it must be easy..it's just understanding the logic Tableau needs...

3. Re: Noob on this calc..Dimension subgroups..??
Kevin Andrist Mar 1, 2012 7:57 AM (in response to Tracy Rodgers)Works great Tracy..thanks for the help!

4. Re: Noob on this calc..Dimension subgroups..??
Kevin Andrist Mar 2, 2012 9:40 AM (in response to Tracy Rodgers)Tracy,
If you can, please take a look at the attached screenshot. The variance calc works fine from a totals standpoint, but how can I assign the NULLs in the Baseline column to zero, such that the variance will yield a numerical result relative to the Scen2B column? The reason the Baseline column has a NULL value is that the OriginDestination pair that exists in Scen2B, didn't exist in the Baseline. Having a numerical value in every Variance column cell is key as the variance column is sorted to determine what change between the Model Baseline and Scenario2B had the greatest impact on cost.
As this calc exists now, a sort only yields an order of change in cases where numbers existed in both Baseline & Scen2B for any OriginDestination pair.
This is one step beyond (different base data) than the orginal question & data, but part of the same analysis.
thanks for any thoughts...

screenshot.bmp 1.8 MB


5. Re: Noob on this calc..Dimension subgroups..??
Kevin Andrist Mar 5, 2012 8:48 AM (in response to Kevin Andrist)I think I have it..I wouldn't have guessed this would work, but it seems to:
The calc below is assigned to/creates a new Measure called "Baseline Shipping Cost"
IF ISNULL(IF[Model]='Baseline' THEN [Shipping Cost] end) THEN 0
ELSE
IF[Model]='Baseline' THEN [Shipping Cost] end
end
If anyone sees an obvious flaw here, please let me know..
thx

6. Re: Noob on this calc..Dimension subgroups..??
Alex Kerin Mar 5, 2012 8:57 AM (in response to Kevin Andrist)You could probably simplify this to:
IF[Model]='Baseline' THEN zn( [Shipping Cost]) end
zn or zero null replaces nulls with 0

7. Re: Noob on this calc..Dimension subgroups..??
Kevin Andrist Mar 5, 2012 9:25 AM (in response to Alex Kerin)Thanks Alex,
That does shorten things up a bit..I wasn't aware of the zn function, good to know.
I did try it and it fails to evaluate to zero when Baseline Shipping cost is null.
I'd guess that the Shipping Cost isn't actually null until it's associated with Baseline, but the zn is only operating on [Shipping Cost] without the association???
My previous formula (logic) applies the ISNULL to (Baseline+Shipping Cost) and it seems to work.

8. Re: Noob on this calc..Dimension subgroups..??
Alex Kerin Mar 5, 2012 9:31 AM (in response to Kevin Andrist)Ah yes, didn't spot that 
maybe:
zn(IF[Model]='Baseline' THEN ( [Shipping Cost]) end)

9. Re: Noob on this calc..Dimension subgroups..??
Kevin Andrist Mar 5, 2012 9:34 AM (in response to Alex Kerin)Good timing..I was just trying that option and it seems to work perfectly
Thanks!