7 Replies Latest reply on Dec 27, 2018 10:45 AM by Prashant Kuruamparambatta

    LOD with condition

    Gowthami Sathiyanathan

      Hi Friends

       

      I am new to Tableau. Kindly clarify the below

       

       

      @

      1. Using condition in LOD.

       

      {FIXED [Product Category]="Office Supplies" : sum([Sales])}.

       

      The above logic sums up all the product category  irrespective of the condition mentioned in . Kindly clarify me, is the usage correct

       

      2. AVG function

       

      To find  average sales value for orders which include office supplies.

       

      Added office supplies in filter condition

       

      Approach 1 :{ FIXED : AVG({ FIXED [Order ID] :sum([Sales])})}

       

      Approach 2: { FIXED [Order ID] :sum([Sales])} and do average on the calculated field. By right click and Measure(avg)

       

      Approach 2 produces difference result than approach 1. I am not understanding why its different.

       

      Kindly clarify

       

       

      Thanks

      Gow

        • 1. Re: LOD with condition
          Chris McClellan

          You probably want to do something like :

           

          {FIXED [Product Category]: sum(if [Product Category]="Office Supplies" then [Sales] end )}
          

           

           

          But that will only return the Sum of Sales when you choose the Office Supplies value out of all the Product Category values.  If you chose another Product Category you would get NULL.

           

          Basically, I'm concerned why you want to do something like this - are you working harder or smarter ?

          • 2. Re: LOD with condition
            Ganapathy Palanimuthu

            Further to Chris's reply, if you want the value to appear against all rows to make a comparison

             

            Calculated field: Furniture_Sales

            {SUM(IF [Product Category] = "Furniture"

            THEN [Sales]

            ELSE 0

            END)}

            • 3. Re: LOD with condition
              tian.yang.4

              hi Gowthami,

               

              try this and datasource is superstore.

               

              { FIXED [Category]:

              SUM(IF [Category]='Office Supplies' then [Sales] END)}

               

               

              { FIXED [Order ID]:

              AVG(IF [Category]='Office Supplies' then [Sales] END)}

               

              YT

              • 4. Re: LOD with condition
                Prashant Kuruamparambatta

                Hi Gowthami,

                 

                It seems you have been using different tool till recently and trying the same logic here for the #1.

                     1. This is not the proper syntax to use LoD. Please refer, Level of Detail Expressions - Tableau  to learn more about it.

                 

                     2. Both the calculation should have resulted in same value unless you are using any filter.

                          a. Approach 1 would be same even if you have filters applied (not Context filters, you may search what that is)

                          b. Approach 2 would change as per filters used.

                          If you remove all the filters used in Approach 2, you will see both approaches results in same value.

                 

                I don't see any reason to explain further as I see you are very close and you are trying different approaches. Please go through the above mentioned link for further clarity. Cheers!

                • 5. Re: LOD with condition
                  Chris McClellan

                  If any 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.

                  • 6. Re: LOD with condition
                    Gowthami Sathiyanathan

                    Thank you Prashant. I am new to the tool, hence got the basic doubt to get more clarification about my understanding

                     

                    Approach 1 : { FIXED : AVG({ FIXED [Order ID] :sum([Sales])})}

                     

                    Here the average ,considers the number of order id's, irrespective of dimension filter condition(I don't mean context filter) and produxe te output.

                     

                    Approach 2 : { FIXED [Order ID] :sum([Sales])} and do average on the calculated field and Measure(avg)

                     

                    Here the average, considers the number of order id's after applied the dimension filter

                     

                    Hence both approach produce different result.

                     

                    And one more clarification that, the FIXED LOD calculation happens before filter condition. Hence the result produce the sum all values with all product categories.

                     

                    Please correct if my understanding is wrong.

                    • 7. Re: LOD with condition
                      Prashant Kuruamparambatta

                      Approach 1:

                      Summing up Sales by each order and then taking an average of sum(Sales) for all the orders. As the outer LoD is not dependent on any dimension, this would give a fixed value and using a non-context filter won't affect the values.

                       

                      Approach 2: will do the same as your inner LoD in Approach#1. As you are aggregating the Sales on OrderID, any change to non-context OrderID filter will change the values.

                       

                      You qouted, "the FIXED LOD calculation happens before filter condition. Hence the result produce the sum all values with all product categories."...Exactly, A Minor disclaimer, Fixed works before dimension/measure filters but after context filters.