8 Replies Latest reply on Oct 25, 2017 2:49 PM by Miles Baker

    LOD: null dimension value = null FIXED value

    Miles Baker

      Hi,

       

      I'm attempting to show state average profit margins vs the national average profit margin. I have created a simple twbx example. The data is just a list of sales of vehicles for three states. NY and CA sell all three vehicle types every month. WY sells at higher margin, but doesn't sell buses and trucks every month. The problem is that the National Margin value is not appearing for WY in months when they don't make a sale. I think I might need some kind of nested LOD calc to make this work, but I'm not skilled enough yet to get it.

       

      Sheet1 shows the problem with all states visible. Sheet2 shows the same but with a filter on the State. My requirement is to have Sheet2 working, as this will be part of a larger dashboard with the state filter affecting all sheets.

       

      My Margin calc is a simple sum([Profit]) / sum([Price])

       

      My National Margin calc is { FIXED [Vehicle Type], YEAR([Date]), MONTH([Date]) : sum([Profit]) / sum([Price]) }

       

      I have used a FIXED instead of an EXCLUDE because I need to use a dimension filter on state in the final dashboard, and FIXED applies above Dimension filters, EXCLUDE applies below it.

       

      Any help would be much appreciated!!

        • 1. Re: LOD: null dimension value = null FIXED value
          Shawn Wallwork

          Thanks for the sample workbook.

           

          Instead of filtering on the filter shelf, move the filtering inside the calculation, like this:

          Then you can use a parameter to select individual states without affecting the national totals. See attached.

          1 of 1 people found this helpful
          • 2. Re: LOD: null dimension value = null FIXED value
            arvindgarg

            I would like to share another approach

             

            Duplicate the same data source

            Then using data blending, take the national average from the duplicated data source.

             

            Please see attached worksheet.

            1 of 1 people found this helpful
            • 3. Re: LOD: null dimension value = null FIXED value
              Miles Baker

              Hi Shawn,

               

              Thanks for this. I had thought that "something" with a parameter would possibly work (removing the dimension filter). But I didn't get all the way there. I would like to get the dimension filtered data working though, as my actual data doesn't have a static set of values (state names) to filter on. Also, Sheet1 still doesn't work with a parameter.

               

              Why your book works seems to make sense to me. Without the dimension filter, there are rows for each of those cells to aggregate. With the dimension filter though, there are no rows. However this seems to violate the documentation which says that FIXED should operate above dimension filters. If FIXED is broken and not obeying the documentation, that also makes sense with why Sheet1 is not working.. to my mind anyway..

              • 4. Re: LOD: null dimension value = null FIXED value
                Shawn Wallwork

                Fixed isn't broken. The documentation isn't wrong. But I'll leave the explanation for what's happening to Bora Beran the leader of the team that wrote LOD calcs.

                 

                • 5. Re: LOD: null dimension value = null FIXED value
                  Miles Baker

                  Thanks, I'm really keen to read his reply.. I'd like to get a better understanding of how the the Fixed LOD calc works. I'm having a hard time getting my head around how the cell highlighted in the picture works. I'd figured the logic behind it would ignore the value of the State dimension and therefore calculate across all rows at Vehicle.Bus, Year(Date).2017, Month(Date).January.

                   

                  Thanks again.

                   

                  carsalestest1.png

                  • 6. Re: LOD: null dimension value = null FIXED value
                    Bora Beran

                    LOD expressions are subqueries joined to the query for the main viz e.g. your viz above would have a query like this (abbreviating date fields instead of writing the full DATEPART('year', ..) etc.)

                    SELECT state, yeardate, yearmonth, VehicleType, sum(national margin) group by state, yeardate, yearmonth, VehicleType where state = 'Wyoming'

                     

                     

                    LOD calculation ignores the where clause so it is

                     

                     

                    Select sum(sales)/sum(profit) group by yeardate, yearmonth, vehicleType

                     

                     

                    Since there is no where clause (fixed ignoring the filter) this will compute the ratio for all states.

                     

                     

                    Now Tableau has to join these two tables to get the final viz and since shared LOD is year, month, vehicle type those would be the join keys.

                     

                     

                    Inner join means only the records that occur in both tables will be kept and rest will disappear. So even though fixed will calculate the correct sum for all states, if a given state hasn’t sold anything in April 2017 the viz itself having been filtered won’t have a row for April 2017. So LOD calc can’t bring the value it computed back into the viz.

                     

                     

                    The main viz determines what marks will be in the viz. LOD expressions just compute values to be associated with those marks. If viz doesn’t have a corresponding mark for something LOD expression computes, it has no place to be shown hence gets dropped.

                     

                     

                    I hope this explains.

                    2 of 2 people found this helpful
                    • 7. Re: LOD: null dimension value = null FIXED value
                      Shawn Wallwork

                      Thanks Bora, makes sense to me, I just can't ever explain it to others (at least with any detail).

                      • 8. Re: LOD: null dimension value = null FIXED value
                        Miles Baker

                        I appreciate and understand the reply. I also don't want to sound like the classic complaining user because I hate being on the receiving end of that too, but I guess I'll have to be.

                         

                        This is a "problem" I encounter in Tableau a lot. Another example that comes to mind is YoY (etc) table calcs only really work as expected if you use discrete time and show missing values. It just hurts to lose the awesome continuous time axis and get the terrible discrete time axis. And if one of our less technical users doesn't know to do this, it actually produces erroneous charts.

                         

                        While I understand where you are coming from, and I know it won't be changed, this sure does give me a lot of pain and makes the product harder and less predictable to use. Tableau is mostly a really great product and I do like it a lot.. this is about the only complaint I really have. I think most of us just naturally expect each series/calculation to be treated as its own mark set. It is very tricky to keep in mind that Fixed is above dimension filter, but its marks ARE filtered by the dimension. While I can keep this stuff in my head and continuously think about how my calcs are affected by sparse data, my colleagues who are not developers can't, and their results from the product are less awesome than they could be... or risk being straight up wrong. The example in this case is, from a user perspective, (less seriously) wrong: not showing national average where one exists. I understand the technical reason, but for me as the guy sitting in the seat with users complaining to him about their dashboard, it is wrong. It gets worse where I try to show an "above/below average" calc instead. The table calcs shown on continuous time axis produce VERY wrong results: marks in the wrong place.

                         

                        Thanks for your time. I think the user above that suggested blending the data to itself is about my only workable solution.