9 Replies Latest reply on Mar 5, 2012 9:34 AM by Kevin Andrist

# Noob on this calc..Dimension subgroups..??

I'm a noob on some calculations and this is one of them.  I'm guessing this will be a no-brainer for someone...

Attached is a file that contains the base data (cols A, B, C)  & a diagram (cols I-N) of what I'm trying to do.

The numbers in col J is the sum of the Baseline (col A) P2W Transition (col B), W2W Transition (col B)..etc...use Excel-Filters to see the contents of cols A & B and I think it'll make sense what I'm trying to do.

Thanks for any help,

• ###### 1. Re: Noob on this calc..Dimension subgroups..??

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

• ###### 2. Re: Noob on this calc..Dimension subgroups..??

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

Works great Tracy..thanks for the help!

• ###### 4. Re: Noob on this calc..Dimension subgroups..??

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 Origin-Destination 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 Origin-Destination pair.

This is one step beyond (different base data)  than the orginal question & data, but part of the same analysis.

thanks for any thoughts...

• ###### 5. Re: Noob on this calc..Dimension subgroups..??

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

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

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

Ah yes, didn't spot that -

maybe:

zn(IF[Model]='Baseline' THEN ( [Shipping Cost]) end)

• ###### 9. Re: Noob on this calc..Dimension subgroups..??

Good timing..I was just trying that option and it seems to work perfectly

Thanks!