12 Replies Latest reply on Mar 21, 2014 9:49 AM by Tom W

Aggregation with 'deep' data structure

I've provided a packaged workbook using a 'deep' data structure.

That is, I have a Metric field and a Metric Value field, rather than having 1 column per metric.

I've setup a couple of Calculated Fields i.e.
PercentageDiscount = If Metric = 'PercentageDiscount' then MetricValue End

PercentageInventory = IF Metric = 'PercentageInventory' THEN MetricValue End

These work fine.

Now what I need to do, is perform a calculation on those two fields at the lowest level and aggregate it up. Effectively I need to do PercentageDiscount * PercentageInventory on a line by line basis then aggregate it.

The problem is, this doesn't even seem to work at the lowest level, as per the calculated field 'DiscxInv', it's just showing blank.

I definitely have the option to produce a dataset which is one metric per column, but i'd like to avoid it as it means I need to do more work in views, tables and proc's in the DB where as I'd rather push the responsibility onto the users in Tableau.

Thanks,

Tom

• 1. Re: Aggregation with 'deep' data structure

http://www.theinformationlab.co.uk/2012/03/27/tableau-for-excel-users-part-2-calculated-fields/

Shouldn't the calc be

sum(PercentageDiscount) * sum(PercentageInventory) ?

Otherwise you are dividing each value and summing the results.  Excellent post linked above.

• 2. Re: Aggregation with 'deep' data structure

Now the problem with that is, if you pull 'Line' out of the Rows shelf so it's aggregation to a total, that formula is going to sum all the discounts then multiply them by the sum of the Percentage Inventory.

This calculation needs to happen at the row level though, otherwise you end up with the wrong result. Does that make sense?

• 3. Re: Aggregation with 'deep' data structure

Yes, I think so.  So, what is the end goal?  Table Calculations allow us to do the type of calculating you want to do, but I'm not sure what your expected end results are.  For example, we can move Line to the "Detail" shelf, and then do a WINDOW_AVG, WINDOW_SUM, etc on the individual values to get a single value aggregated to the proper level.

So, what are you wanting as an end result/view?

• 4. Re: Aggregation with 'deep' data structure

For this example, I'd like the DiscxInv at the Store Level. So, StoreID on the Rows shelf.

I don't actually care about the lowest level numbers.

In my real-world example, the store has a number of attributes which form a bit of a hierarchy like Geography, Region etc. So in real world, I'll be rolling up from Store to Region etc. But for the purposes of this example I think StoreID is fine.

• 5. Re: Aggregation with 'deep' data structure

I'd need to see a mockup of your expected results, particularly what values you want displayed and at what level of aggregation (Store ID, it seems)

• 6. Re: Re: Aggregation with 'deep' data structure

For now this is all I need to achieve;

• 7. Re: Re: Re: Aggregation with 'deep' data structure

OK, that's what I was hoping.  So, we move Line to the Level of Detail, and use a WINDOW_SUM calculation on the measures, with an advanced compute using for each set to: Addressing on StoreID, then Line, Restarting Every StoreID (see screenshot above)

If your actual view has more dimensions included, you may have to adjust the portioning/addressing to account for those dimensions.  I can help you with that, if needed.

We must also turn of "Stack Marks" under the "Analysis" menu to get a proper text table layout.  The IF FIRST()==0 optimization is added to each Table Calc, so only one result per row is returned (rather than one for each Line item)

I hope this helps!  All of the techniques used for this are discussed and demonstrated on Jonathan Drummey's blog Drawing With Numbers.

• 8. Re: Re: Re: Aggregation with 'deep' data structure

http://drawingwithnumbers.artisart.org/customizing-grand-totals-in-tableau-v8-the-stacking-snag/

Most of what was needed is discussed in this blog post by Jonathan.  I hope it helps!  It took me several months to wrap my head around some of these concepts, but with practice, they become like second nature.

1 of 1 people found this helpful
• 9. Re: Re: Aggregation with 'deep' data structure

Thanks, I'll give that page a read.

This solution looks good for the example we've discussed.

If I add in extra dimensions, I'm having some problems with the calc. I'll spend some time to try and figure that out, but the thing which is most concerning to me about this appraoch is that the Table Calc config is specific to the dimensions / attributes in the table at the time.

So lets say I have Region > Category > Store and setup the table calc for that. If I decide I want to pull the store out and take it up to category level, I have to re-write the table calc, which isn't ideal for interactive analysis.

• 10. Re: Re: Aggregation with 'deep' data structure

Yes, it can be difficult to set up Table Calcs to work at various levels--but it can be done; there are a few threads out there about Table Calcs and Hierarchies; I know Shawn Wallwork had a scenario recently that Jonathan Drummey helped him out with.

Anyway, I hope you find a workable solution.  I'm happy to take a look at any problematic table calcs, although I probably won't be able to figure out the complexities of a hierarchy.