-
1. Re: CALC HELP - how to add/subtract row-level data
David Andrade Jul 14, 2013 10:31 AM (in response to David Andrade)My wicked smaht co-worker Amanda Gessert helped me work out a possible solution to my question, so I thought I'd share so anyone else with the same question can see what we've done here.
-
2. Re: Re: CALC HELP - how to add/subtract row-level data
Jonathan DrummeyJul 15, 2013 5:29 AM (in response to David Andrade)
Here's an alternate solution, which gets at Amanda's point about adding a new value to the list of possible Entry Types, it's a variation of the Custom SQL for custom grand total solution at http://drawingwithnumbers.artisart.org/customizing-table-calculations-part-3/. I first copied out the data and pasted it in, then used Custom SQL to duplicate the data, adding a column to identify the Source (original or new header). Once I had that, then I created a calculated field called New Entry Type with the following formula: IF [Source] == "Orig" THEN [EntryType] ELSE "H" END. Then the New Entry Type is used in the text table instead of the Entry Type. You can still filter on the original entry type and the totals will remain.
Another technique instead of duplicating all of the data would be to UNION a SELECT DISTINCT query to get all the combinations of date/Brand/Profile, add a new Entry Type H, then use a table calc to assign a value to H.
As a general approach, the maximum level of detail in a view is the combination of distinct values of your dimensions, Tableau won't increase the level of detail beyond that. So, to show "extra" rows, we need to find a way to increase the level of detail. Using a custom query or subquery to UNION in some extra data is one technique, another is making use of the Grand Total. A third technique is to go to the worksheet level and use a separate worksheet for H then merge the two on a dashboard.
Jonathan