9 Replies Latest reply on Oct 12, 2016 7:43 AM by Mary Gatmaitan

# Fixed LOD and Dimension Filters

Hello All,

I have a conundrum involving the use of a FIXED LOD expression and a dimension filter.  It feels like I should know the answer to this, but at present it eludes me.

Problem setup:

In my example workbook, I have Counts of applicants by Department and *** across four Years.  In a crosstab at the LOD equivalent to my data's granuarity, I'd see 150 applicants across the four-year period, no Males for Department B in 2012 and no Females for Department C in the same year:

I want to build a filtered table to show the proportion of Female (or Male) applicants to each Department across the time period looking like this:

My LOD Total calculation is the expression:

{

FIXED

[Department]

,[Year]

:

SUM([Count])

}

and Pct uses this total to get the percentage:

SUM([Count]) / SUM([LOD Total])

The problem is that Department C should show 35 total applicants instead of 33.  If I add Year to the viz, I can see that the LOD Total is ignored for that Department in 2012:

I recognize that I could fix the first viz by removing Year from my FIXED expression, but I would lose the ability to drill down to the year level (at least without creating a second LOD Total at that level of detail).

I assume that the empty cell is causing the total to be ignored for the affected row.  (i.e., There are no Female applicants for Department C for 2012, and there is no corresponding row in the data for that Department-Year-*** combination).   Is this true?  Or is something else going on?  Most importantly, is there some means for me to fix my viz to get the correct totals?

• ###### 2. Re: Fixed LOD and Dimension Filters

I think you are correct about the issue. When you filter by ***, the row containing the count of males that doesn't have a corresponding department/year row for females is dropped from the table. So, although the LOD expression is calculating the value correctly, it isn't included. One way to work around this would be to use a parameter and new calculated field. Here's what I did:

Create a string parameter with values of All, Female, & Male. (I called it [*** Parameter].)

Create a calculated field with the following formula, which basically filters to the count based on the parameter selection:

if [*** Parameter]="All" then [Count]

elseif [*** Parameter]=[***] then [Count]

end

You can then use the calculated field in the calculation of the percent instead of the original count. The row with no female (or male) applications will still show in the table since there is a value for total applications (the LOD calculation) so that value is now included in the totals. If you wanted, you could add an "else 0" before the end statement so that the missing row will show in the table the same way the rows with 0 currently appear for consistency.

Hope this helps

-Marc

3 of 3 people found this helpful
• ###### 3. Re: Fixed LOD and Dimension Filters

Great solution Marc.  Providing a null for a undesired value rather than utilizing a filter keeps the data desired in the view and allows the calculation to work.

• ###### 4. Re: Fixed LOD and Dimension Filters

Thanks, all.

Since Tableau documentation indicated that dimension filters were ignored in FIXED LOD's, I thought I was ok.  Your explanation/solution suggests that there is a least one caveat to that statement.  Thanks, again!

--Jason

• ###### 5. Re: Fixed LOD and Dimension Filters

This is like an inverse LOD for comparing total vs specific departments. Solved a two week on and off head-wall bashing mystery for me. Thank you.

This is super useful for dual-axis histograms comparing departments to corporate totals.

• ###### 6. Re: Fixed LOD and Dimension Filters

Hi Kevin!  I am having a similar issue and my head is starting to hurt from all the head-wall bashing I have been doing.  Will you be willing to share how you solved your issue in comparing total vs specific departments?

My issue is such that although I was able to create a calculated field for the system total and average, this number changes when I filter out some of the specific departments.  I would like for it to be such that the system totals will not change, and are not affected by the department filters.

Any help is greatly appreciated!

• ###### 7. Re: Fixed LOD and Dimension Filters

Hey Mary,

You can try using a set and hiding the values not in the set (values you filtered by) so that your view only shows the departments you want but the calculations are done across all data.

-Lisa

• ###### 8. Re: Fixed LOD and Dimension Filters

Hi Mary,

I'd try Lisa's response if you are looking for filtering multiple departments. Though I'm not 100% how you can select different combos using sets (I haven't studied this yet.)

My solution involved selecting only one department at a time and comparing it against the total, a separate measure value in the view. So my parameter is the department I want information on, and I use the Parameter Control to filter between departments, and not the filter function.

If this doesn't make sense let me know and I will explain in more detail.

edit: just to note, you're running into one of the most popular requested features for Tableau, which is the ability to select multiple values of a parameter at a time:

In Tableau 10 you can use a parameter to filter across datasets: As Requested, You Can Filter across Data Sources in Tableau 10 | Tableau Software

You might be able to jerry-rig this into having one table with the departments, and the other table with the totals, and then joining the table on itself.. would be a bit of work though. ...Ask Lisa about the sets!

• ###### 9. Re: Fixed LOD and Dimension Filters

Thanks! Let me try that!