This is a great solution! But, I have a follow-up question: Let's say we want to calculate a running sum of the number of envelopes shipped. Adjusting just the Calculation to use a running_sum instead of sum results in the running sum for 2011 alone--see the sheet Cumulative Calc. This is fine, but another wrinkle: we want the running sum from the beginning of time (in the data). If I change the Year Ship Date filter to a look up calculation, then all of the months without data disappear--even if I select "Show Missing Values" for the months tab-see the sheet Cumulative Calc Date Filter.
I've looked at other solutions, and the most appealing seems to be custom blending data in SQL. I'm trying to avoid this approach for various reasons. Are there any other approaches that might work?
Hi everyone, I have a similar question that I just can't figure out. I've attached a sample workbook using superstore data. I've created two sheets and placed them in a dashboard: in Sheet 1 I have profit and sales for Furniture, and in Sheet 2 I just have sales. I'm also using parameters for city and state so that if I want to collapse the data to, say, just states it will work across both sheets (kind of as a proxy for drilling down across multiple sheets in a dashboard).
What I'd like to be able to do is ensure that both sheets show the same cities so that the rows match up, so I need the cities with nulls to show. I know there's the option to show empty rows which I used in Sheet 1. But the problem with that option is I filtered for just Alabama, Arizona, and Arkansas, but all the other states are still showing up. I tried some of the other methods in this thread but I couldn't get it to work right.
Any help or suggestions are appreciated!
Superstore example.twbx 1.2 MB
Would this work for you?
All I've done here is add an index() to the column shelf, set it to compute using = Category...so it basically just numbers the category 1,2,3...and then used the index(), instead of the category filter to only show the category I want. As this is a table calc filter it filters the Viz, without filtering the underlying data (due to Tableau's order of filter operations). Although the default index just numbers them in the order it gets to them (alphabetic here), we can control the order (using the sort within index())...might be of help if your real world situation would require such a thing (eg. I often use this trick to filter out all but last year, meaning it only shows last year, but can still perform a YoY calc...so I sort the years ascending, and index()= in my filter)
Hope this helps.
btw I know your question is related to the original one from Shawn, but you'd probably get more visibility if you'd started a new thread (as this thread is so old). I only caught this, as I was looking through the first page, and saw a question from Shawn!! There is a crows nest, which shows all unanswered questions, and many of us trawl through to check we've answered everyone (or as many as possible!), and this wouldn't have appeared.
Keep Sheets in Line.twbx 1.2 MB
How about the attached? Instead of trying to create separate worksheets and synchronize the display (which doesn't really work anyways for long lists because Tableau doesn't synchronize scrolling) I did the equivalent of a SUMIF() to create measures for Furniture Profit, Furniture Sales, and Office Supplies Sales and put them all on the same worksheet, then used a custom Tableau hierarchy for the drilling:
v9.2 workbook is attached.
Superstore example jtd.twbx 1.2 MB
Thank you both! I was able to get both methods to work and these should be very helpful
Dear Joe, All,
Is there a way of displaying Continent - Australasia, and Furniture - Large Box only, as it is on the attached screenshot without any other containers?
In other words - in this scenario, if you uncheck 'Warp Bag' all table will be gone. Can we somehow leave it on to show 0 value?
Asking as I have a data source connected to excel file working as a scaffold, keeping all necessary rows and order. Tableau folds rows automatically and if I select "Show Empty Rows" I'm getting rows from another section, which I don't want to show. What I need is to display all values (even if they don't exists) for a given section.
So in our example - Section would be 'Continent' and I must display 'Large Box' = 0, providing that 'Wrap Box' (and other data points) are not displayed.
Is this doable?
Thanks in advance for your replay.
I have a bit of an issue. We have a Goal for Office 1 and that goal is then broken down by Teams (from that office). I'm using Tableau's Grand Total function to sum up the team goals so that the Office 1 Goal in the visual matches the offices aggregate goal (at an office level). However, Team 8 is not shown in the visual because they have yet to produce and Office 1 Team 8 numbers for this month yet. I need Team 8 to show even though they've not done any Office 1, Team 8 transactions.
I know this is basically the opposite of what I'm telling Tableau to do in the Filters section, but just wondering if there is a way.
I can see the original post here is pretty old but it looks like a good place to get help solving Null related issues.
I have a problem with a continuous dimension I am using for my x-axis across multiple sheets. I essentially want to be able to use this variable as a filter across sheets in my dashboard while maintaining common reference across the sheets - this works fine when Nulls are shown of course:
The start and end of the series aligns between all the sheets as desired (0 and 17)
But unfortunately many of my data items have null values - I really don't want to show the "Null" category in any of the displays as the gaps in the data already express this but of course filtering them out or hiding or excluding them results in:
As can be seen in the first chart the series no longer starts at 0 and the end of the second chart no longer ends at 17. The idea is to have a dashboard that lets the user look down through the charts at the same location - eventually I want to remove the x axis label from the top two charts and only rely on the third but this misalignment is obviously a problem.
I know I could fix the axis at zero etc but some of the data I am dealing with is in the 000's and the idea is to be able to filter into specific ranges and having the axis fixed is not a workable solution.
I want to be able to remove (or really hide I guess) the nulls while still allowing this to align properly - I realise Tableau is doing what it should but its not really what I want.
Thanks as always for any help - workbook example attached.
Null.twbx 37.4 KB
1 of 1 people found this helpful
So dispute my comments regarding this being an "old thread"....it actually got answer from several people, after a subsequent question was added much later, (including Jonathan D), so I (obviously) don't know what I'm talking about!
With regards your issue. One way I've found of forcing an Axis Range (which I think is what you want here) across sheets is to use Reference Lines.
Sol first I created an LoD for the MIN and MAX [From]...across the whole data set. As a FIXED LoD this is calculated before any "regular" filters are applied, so it is not affected by filtering out the NULLs
So I created
[From MAX Value]
[From MIN Value]
I then bring these into the detail pane, so I have access to them in the reference lines
I then set up the reference lines for each of these measures, in each worksheet (MIN example shown below)
As we now have a reference line at 0 and 17 for each chart....which forces the axis to that range.
In the attached I've left them visible, so you can see what they are doing, but in your final version, set the reference line to "none" and they are no longer visible, but ensure the range goes from 0-17 in all charts.
Hope that helps and makes sense.
Many thanks for your response and idea - greatly appreciated.
One of the issues is that the "From" filter is no longer effective in only showing the filtered values on the axis - it retains the extremes of the data on the axis (i.e. 0 - 17). One of my issues is that I am dealing with a data set that might go from say 0 - 4000 and I want the user to be able to zoom into specific ranges (say 900 - 1000) with only those reference points shown on the x-axis - i.e. the axis needs to be dynamic and shown only the filtered ranges to make zooming on the data easier.....
I had tried using reference lines but was unsuccessful (just based on the min/max of the "from" (filter) field) - is there a way to make these dynamic in the way described?
1 of 1 people found this helpful
Ah yes...this is because the MAX and MIN LoDs are calculated before any filters are applied....
However we can "bump" a filter up the calculation pipeline, by making it a "context" filter.
Now the From filter will affect the calculations...and so change the range (on all the charts).
This looks promising - after doing that I am still finding the range shown does not seem to match between the charts, i.e.:
Notice the end value of the third chart (10) vs the first and second (11) - am guessing this is because there are also null values in the middle of the dataset - i.e. the max and min theory works when the nulls are at the beginning and end but in the middle I am not so sure - if the filter happens to be set somewhere in the null range for one of the datasets that is not necessarily the true end (max) ......?
1 of 1 people found this helpful
So the reason for this is that you are using Gantt Marks, and they are sized in Len.
So what happens, if we take Rut_No_Nulls is that there is a Rut Category at 10.2 (our extent only goes to 10.4), but this then has the Len 0.9, so the line is then further extended to 10.2+0.9 = 11.1 (as Tableau creates the Axis range to include all it "needs" to draw)
So we can either change the mark type to something like Line (I'm not sure what the final goal is, or what this is showing, so not sure if this is an option). OR we can adapt the Len Calculation
[Len to Use]
IF [From]+[Len] > [From MAX Value] THEN 0 ELSE [Len] END
So this stops it drawing anything beyond the MAX From (you could create an extra check on MIN too)
Simon - this looks like a perfect solution! The Length attribute is important as is the mark type - this data is showing deficiencies along a length of asset and the length of section is key as is the linear representation. I have set a similar argument for the min value as well as this is equally likely to occur.
Out of interest do you know if there is any difference between "excluding" nulls as opposed to "hiding" them? in my example the effect seemed to be the same..... I had hoped that hiding them would have done just that but it seemed to simply exclude them.
Thanks again for your help - it was really useful and greatly appreciated