
1. Re: Fixed level LOD with context filters worked well but results are diff when i place it on column shelf
Sujay Paranjpe Feb 1, 2018 2:28 PM (in response to Vaibhav Verma)Hi Vaibhav Verma,
I think you need to understand the context filter well. When you apply the context filter, first your data set filtered and then the LOD is applied on the resulting data set.
For example. when you put YEAR (Order Date) in Context ans select 2014, first your data set is narrowed down to all lines belonging to 2014, and then LOD is applied.
When you take YEAR (Order Date) off the context and then place it on column or row shelf the LOD is applied to the entire data set.
what is the difference between context filter and normal filter?
Best,
Sujay
If this post assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.

2. Re: Fixed level LOD with context filters worked well but results are diff when i place it on column shelf
Vaibhav Verma Feb 1, 2018 2:43 PM (in response to Sujay Paranjpe)Hi Sujay,
Thanks for the reply. I get the differnece between a normal filter and context filter. On the column shelf, i cant make [YEAR] as context. The results which i see using context filter [YEAR], I want to see the same results visually on a dashboard, so that we dont have to toggle between years. Do you have any workaround to that ?
Vaibhav Verma

4. Re: Fixed level LOD with context filters worked well but results are diff when i place it on column shelf
Sujay Paranjpe Feb 1, 2018 3:17 PM (in response to Sujay Paranjpe)There you go.
Created using Tab 10.0.5
Best,
Sujay
If this post assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.

719914.twbx 1.2 MB


5. Re: Fixed level LOD with context filters worked well but results are diff when i place it on column shelf
Vaibhav Verma Feb 2, 2018 12:15 PM (in response to Sujay Paranjpe)Hi Sujay,
Unfortunately, your solution does not solve my issue. I dont want sales value to change based on subcategory, thats why i was using "sales_lod" calculation. In your solution, the sales value will change if we toggle subcategory filter because you are not using LOD and you have also removed all the filters. That was my whole point of making a LOD calculation.
Thanks
Vaibhav Verma 
6. Re: Fixed level LOD with context filters worked well but results are diff when i place it on column shelf
Daniel Ferrer Feb 2, 2018 1:06 PM (in response to Vaibhav Verma)1 of 1 people found this helpfulHello Vaibhav,
Your issue has a pretty simple solution.
Your problem is that your LOD doesn't FIX by year, so when you are not filtering a year (Year of Order Date = All) your LOD is returning a sum of all years.
Your ratio calculation in the other hand is using a ViewLevel calculation ([Quantity]) divided by a LOD calculation ([Lod_sales]).
So, when you add year to your VIEW, you affect your Quantity field, but you don't affect your LOD_Sales field. So you are diving the sum of sales for ALL YEARS by the Quantity of EACH YEAR.
To resolve that, simply add the YEAR to your LOD.
Change your LOD calculation to this code and give it a try:
{FIXED YEAR([Order Date]) : sum([Sales])}
I believe this should get you your expected results.
Cheers

7. Re: Fixed level LOD with context filters worked well but results are diff when i place it on column shelf
Jonathan Drummey Feb 2, 2018 1:23 PM (in response to Vaibhav Verma)3 of 3 people found this helpfulHi,
Sujay's new [ Sales ] measure (note the spaces in the name) actually uses an LOD expression and does not change results based on the SubCategory filter so it's returning accurate results when I build out a view similar to your original.
I can explain what is going on, it's an order of operations issue but it's not around the use of context filters, it's about the level of calculation. Here are two everything we need to explain what is going on is right in the next two screenshots:
This is the original view showing the pills:
This next view has all the same fields and same filter settings (so all years are shown) with a slightly different rearrangement  I used a Measure Names/Values crosstab for the measures and turned on Subtotals, and added a couple of calcs that show an accurate result:
What is happening is the following:
1) The context filters are applied.
2) Tableau is computing the Lod_sales result (2.3M) and returning that as a record level value. Note in the lower screenshot the Total for 2013 is showing that 2.3M for every record, *not* the roughly .47M that you'd expect there based on your filtered view. So the denominator is not being accurately computed.
3) The ratio calculation is [Quantity]/[Lod_sales]. This is a record level calculation so ratio is being computed for each and every record. I can tell it's a recordlevel calculation because there are no aggregation keywords being used for either field in the calculation.
4) The ratio calculation results are summed back to the viz Level of Detail (Year and Category), and the sum of all those results ends up at a ratio value of 0.0007 for Furniture/2013 due the overly large denominator from step #2. I can tell that they are summed because of the SUM() keyword on the ratio pill.
The reason you see different results when you use the Year filter is that the Year filter is removing the records and changing the denominator, whereas in the two above screenshots with Year in the viz level of detail as a dimension the Year is not partitioning/bucketing the Lod_sales measure the way you are expecting. This is because the formula of the Lod_sales measure is that using a FIXED LOD expression at the data source level and Tableau's definition of a FIXED LOD expression is that the FIXED LOD is computed *before* the Year dimension is taken into account. So that's one way order of operations matters.
I made two changes for the two Fixed Year measures in the second screenshot:
1) Therefore the Fixed Year Sales measure has the formula {FIXED YEAR([Order Date]): SUM([Sales])} so it will partition/bucket on the year and we can see it changing for each year.
2) The Fixed Year ratio is defined as SUM([Quantity])/SUM([Fixed Year Sales]). This is necessary for computing an accurate ratio because the Fixed Year Sales needs to be aggregated at different levels of detail and if we didn't SUM() before the division we'd end up with an inaccurate value. This order of operations (aggregate, then divide) is also necessary for many other setups for the data (for example if either of your quantity or sales measures had negative values), Lari McEdward did a fine Tableau training video on this:
Aggregation, Granularity, and Ratio Calculations 4 min What is covered: Aggregation and Granularity The Detail Shelf Aggregation in Calculations Profit Ratio Calculation Summary . So that's the second way order of operations matters.
v10.1 workbook is attached, let me know if you have any questions!
Jonathan

8. Re: Fixed level LOD with context filters worked well but results are diff when i place it on column shelf
Vaibhav Verma Feb 2, 2018 2:01 PM (in response to Daniel Ferrer)Hey Daniel, that pretty much worked for me in the dummy data. I will try to put this solution into my actual dataset. I think it should work there as well . Thanks a lot

9. Re: Fixed level LOD with context filters worked well but results are diff when i place it on column shelf
Vaibhav Verma Feb 2, 2018 2:01 PM (in response to Jonathan Drummey)Thanks for the solution Jonathan.