4 Replies Latest reply on Feb 7, 2018 3:04 PM by Yuriy Fal

    Requesting Clarity on FIXED LOD

    Shawkath Khan

      Hello Tableau Enthusiasts,

       

      Struggling with a situation while using LOD at work. I have created a sample of the scenario using SuperStore data.

       

      I have few crosstabs with the set of measures and measure/measure. There should be a business rule applied on to these crosstabs. Among the list of measure used we are expected to apply/filter the dimensions to few measures and not to the others. I have explained it the in the screenshot below and attached the sample twbx (using 10.2v). Let me know if you needed the viz to be created in the latest version of Tab desktop.

       

      fixed lod.png

       

      What's the understanding while posting this:

       

      As per my understanding, as I have used the FIXED expression, the Manufacturer, State fixed shouldn't be applied as I have fixed the OrderDate, Sub-Category, Region.

      measure1.png

      What's the issue now:

      But then, the data on CustomMeasure1 is filtered by using State & Manufacturer filter.

       

      As per this discussion, I also realized that the condition is written on the calc, IF [Sub-Category] = 'Accessories' may be the reason why it is not working?

       

      Let me know if my approach is right, and if it is not, then kindly advice.  Please let me know if I am missing any info that's required on this discussion. Thanks in advance.

       

      Note: Humbly expecting a quicker solution or a workaround.

        • 1. Re: Requesting Clarity on FIXED LOD
          Shinichiro Murakami

          Hi Shawkath

           

          I am very uncertain what is your issue, but if you expect Calculation 1 unchanged, below is the right formula.

           

           

          Thanks,

          Shin

          • 2. Re: Requesting Clarity on FIXED LOD
            Shawkath Khan

            Hi Shinichiro Mikami

             

             

            Thank you for the response. Here is the requirement I am working on: In my Direct Measures crosstab, I would like to apply State, Manufacturer filter on all rows except the CustomMeasure1. However, I would still like to filter all rows using OrderDate, Region. Due to which I used FIXED LOD. Thanks for taking time for discussing this.

             

            lod.png

             

            -Shaw

            • 3. Re: Requesting Clarity on FIXED LOD
              Łukasz Majewski

              Nested LoD may be tricky especially if you have outer FIXED and inner EXCLUDE/INCLUDE

              As far as I remember those inner expressions are converted to FIXED and granularity is either inherited from the outer one and results either aggregated more or repeated.

              Nested LOD Expressions

              • 4. Re: Requesting Clarity on FIXED LOD
                Yuriy Fal

                Hi Shawkath,

                 

                As you're using Year of Order Date as a Filter --

                actually the calc is DATEPART('year', [Order Date]) --

                you'd like to re-write your CustomMeasure1 (and others as well):

                {

                FIXED YEAR([Order Date]), [Region] :

                SUM(IF [Sub-Category] = 'Accessories'

                THEN [Number of Records]

                END)

                }

                 

                1) With your current calculations we're catching

                the following effect (because of sparse data):

                 

                -- not every [Sub-Category] is sold

                in each [Region] on each [Order Date].

                 

                So filtering by either [Manufacturer] or [State] (or both)

                may actually remove some datasource rows -- the specific

                [Sub-Category],[Region],[Order Date] value combinations --

                from being aggregated into the Marks on the view.

                 

                2) BTW, even if a FIXED LOD is calculated before a view

                (formally at a Row-Level), the outer aggregation -- such as

                SUM( [CustomMeasure1] ) -- to be calculated on Marks

                after Regular Dimension Filters applied, hence the result.

                 

                Hope this could help understanding.

                 

                Yours,

                Yuri