7 Replies Latest reply on Sep 7, 2016 5:16 AM by Hugo Portefaix

    FIXED method won't take all dimensions values into account

    Hugo Portefaix

      Hello guys,

       

      I've had a problems for a few days now and I think I've ran into an impass.

       

      Here's what I'm trying to do.

      I have two dimensions put side by side in the lines and one measure (Population) as columns.

       

      Dim 1 = Country Region

      Dim 2 = Countries

      Do Dim 1 is broken by Dim 2.

       

      As a test, I would like the Population to show the population number for each region, and not for each countries. It would give something like this :

       

      Africa : Morocco = 654 M

      Africa : Tunisia = 654M

      ...

      Europe : France = 350M

      Europe : UK = 350M

      ...

      North America : USA : 450M

      etc.

       

      For this, i use FIXED : {FIXED [Region] : Sum(Population) } , so the Population isn't summed by countries.

       

      The reason I would like to do this for is because I would like to add two other metrics and use this number to calculate a ratio (% of region population). So you'd have :

                                    Region Population     Country Population     % Region Population

      Africa : Morocco = 654M                          |   33M                        |  5%

       

      Problem is, some countries in my data set were created after 2000. And weirdly, rather than giving me the same total for each country in one region, Tableau and the Fixed method will return smaller totals for countries appearing in my data set after 01/01/2000.

       

      Some help would be more than welcome here as I've been struggling on this for too many hours...

       

      Thanks a lot !

       

      Hugo

        • 1. Re: FIXED method won't take all dimensions values into account
          Andrew Watson

          Sounds strange, your formula looks correct. Can you confirm the region names are all completely identical - i.e. no hidden spacing, capitalisation, etc?

           

          If you just put the region name into a column does it return the list as you would expect or does there appear to be some duplication?

          • 2. Re: FIXED method won't take all dimensions values into account
            Hugo Portefaix

            Hi Andrew,

             

            Thanks for your message !

             

            I can confirm, the Region Names come from a dim table we use all the time, they're all identical. And clearly the only pattern between those countries who have less total than the others are from the creation date. If I filter my report on a date after their creation, then they will be showing the same totals as the other countries.

             

            Everything is working fine if I remove the breakdown by country as well. The total is the one I want to get.

             

            Only those countries are messing up with my report :/

            • 3. Re: FIXED method won't take all dimensions values into account
              Andrew Watson

              Can you post a tableau packaged workbook? Or a sample of the data in a spreadsheet containing some of the 'dodgy' regions?

              • 4. Re: FIXED method won't take all dimensions values into account
                Hugo Portefaix

                Hey !

                It will be complicated to do that as I'm working with lots of confidential data that I obviously cannot share, even just a sample :/

                 

                However, I managed to find a clue : the FIXED method will work fine until I add a third dimension, which is the date (I need to be able to be able to filter on certain periods of times).

                 

                So since I don't have all date entries for some of my countries,the FIXED will count the total differently, and mess up will all of my ratios.

                • 5. Re: FIXED method won't take all dimensions values into account
                  Andrew Watson

                  I'm surprised the FIXED stops working once you add a date filter. Is your date filter in context? If so that could be the reason - you can remove it from context and this problem should be solved.

                  • 6. Re: FIXED method won't take all dimensions values into account
                    Jonathan Drummey

                    I believe I can explain what is happening: When we use the results of a FIXED LOD expression as a measure there are *2* aggregations: the inner aggregation that is inside the FIXED LOD, then an outer aggregation when the results of the LOD are aggregated to the vizLOD. So just like other aggregate measures are dependent on the vizLOD, so are FIXED LOD expressions that are aggregated.

                     

                    This means that if there is sparse data (as indicated in your data by countries that don't have data for some years) then the results of aggregating a FIXED LOD result can be nonsensical as it is aggregated more for some values than others. It also means that the results of aggregated FIXED LODs can be affected by regular dimension filters since they apply prior to the aggregation to the vizLOD.

                     

                    Here's an example from Superstore where I'm using {FIXED [Category] : SUM(1)} as Category Number of Records with that in the view twice, once as SUM(Category Number of Records) and once as the Fixed Sum dimension with the formula {FIXED : SUM([Category Number of Records])}. When I filter out Furniture the Fixed Sum doesn't change but the SUM(Category Number of Records) does:

                     

                    Screen Shot 2016-09-06 at 10.08.27 PM.png

                     

                    There are several workarounds, I don't know which would work best for you:

                     

                    1) Instead of using SUM({FIXED LOD expression}) use AVG(), MIN(), MAX(), or ATTR() for your aggregation of the Region total.

                     

                    2) Use INCLUDE and EXCLUDE instead of FIXED.

                     

                    3) Make the entire calculation an LOD expression so you can absolutely control each result.

                     

                    4) Use a table calculation. Tableau already has a built-in % of total table quick table calculation that can generate SUM(Population)/TOTAL(SUM(Population)) in a couple of clicks.

                     

                    Jonathan

                    • 7. Re: FIXED method won't take all dimensions values into account
                      Hugo Portefaix

                      Hello Jonathan,

                       

                      Yes ! That did the trick, particularly #2.

                      I didn't know INCLUDE and EXCLUDE existed, it solved my problem in 5mn.

                       

                      I just had to exclude the country population from my denominator so only region population would be taken into account in all country lines.

                       

                      Thank you very much for your help !