5 Replies Latest reply on Oct 23, 2017 1:52 AM by Abhilash Sharma

    Calculated Field Explanation required

    Anuj Chopra

      Hi All,

       

      Can someone please help me understand the following calculated field in simple english please:

       

      { FIXED [A],[B],[C]:SUM({EXCLUDE [Date]:SUM(IF MONTH([Date])=6 THEN [Measure-1] ELSE 0 END)})}

       

      Thanks in advance.

        • 1. Re: Calculated Field Explanation required
          Abhilash Sharma

          Hi Anuj,

           

          The above calculation can be explained in the following manner:

           

          You are basically excluding the 'DATE' and you are also checking if the month is 6 i.e. 'June'. And then you are fixing the value of the measure on 3 dimensions A, B and C.

           

          Example explained using Superstore data:

          { FIXED [Category],[Subcategory],[Product]:SUM({EXCLUDE [ShippingDate]:SUM(IF MONTH([ShippingDate])=6 THEN [Sales-1] ELSE 0 END)})}

           

          You are excluding Shipping Date, and then checking if month of shipping date = 6 then you want to fix the value of the Sales based on Category, sub-category and Product dimensions.

           

          You would do this if you have different levels of data in your report and want to ensure that it is not showing incorrect data.

           

          If it helps, please mark helpful an/or correct answer.

           

          Thanks,

          Abhilash

          5 of 5 people found this helpful
          • 2. Re: Calculated Field Explanation required
            Jim Dehner

            Hi

            See Abhilash's answer it is corrent - I wuold add also that using Fixed will create permutations based on the combination of dimensions that preceed the colon(:) and perform the aggreggation on the formula that follow the colon - so by using Fixed on 3 dimensions A,B and C you have created results for all the combinations of those dimensions.

            You now have a variety of options on ho to aggrgate them as you bring them into your viz  - ie Sum(), Avg(), Max, Min() - gives you a lot of flexibility

             

            Jim

            1 of 1 people found this helpful
            • 3. Re: Calculated Field Explanation required
              Abhilash Sharma

              Thank you Jim for the acknowledgement.

               

              @Anuj: Does that answer your query? Or is there anything that you still did not understand?

               

              If it helped, please mark helpful and/or correct answer.

               

              Thanks,

              Abhilash

              • 4. Re: Calculated Field Explanation required
                Anuj Chopra

                Hi Abhilash,

                Thanks for your explanation. However I am still having some questions.

                 

                { FIXED [Category],[Subcategory],[Product]:SUM({EXCLUDE [ShippingDate]:SUM(IF MONTH([ShippingDate])=6 THEN [Sales-1] ELSE 0 END)})}

                 

                1. In the above calculated field, if we do not put any dimensions after FIXED then what will happen?

                2. What do you mean by value is getting fixed? (What effect is this happening on the end result ?)

                3. What is meant by excluding the Date? (What effect is this happening on the end result ?)

                 

                Questions might sound stupid but I will appreciate it if you could explain me like a 5 year old.

                 

                Thanks,

                Anuj

                • 5. Re: Calculated Field Explanation required
                  Abhilash Sharma

                  Hi Anuj,

                   

                  These are referred to as "Level of Detail" or LOD expressions. It is a very complex topic to understand. So I suggest you put some time in understanding the concepts of LOD.

                  Please visit this link and read thoroughly, it will help you: Overview: Level of Detail Expressions

                   

                  To answer your questions:

                  1. In the above calculated field, if we do not put any dimensions after FIXED then what will happen?

                  If you do not put any dimension after FIXED, then Tableau will calculate values based on highest level of data. Please refer to sample screenshot below, here CF shows SUM(SALES) for across all data

                  2. What do you mean by value is getting fixed? (What effect is this happening on the end result ?)

                  If means you are fixing the values of your measures, even though the report has data of different levels

                   

                  3. What is meant by excluding the Date? (What effect is this happening on the end result ?)

                  EXCLUDE will mean the calculation of your measure, will not be based on Date since it has been excluded but it will be done on a level above than Date. If you do not exclude, the measure values will be calculated on Date level assuming that's the lowest level of data. Refer below screenshot, where Category is in EXCLUDE so the Sales is getting calculated on Department level

                   

                  Hope this helps.

                   

                  -Abhilash

                  4 of 4 people found this helpful