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

# Calculated Field Explanation required

Hi All,

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

• ###### 1. Re: Calculated Field Explanation required

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.

Thanks,

Abhilash

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

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

Thank you Jim for the acknowledgement.

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

Thanks,

Abhilash

• ###### 4. Re: Calculated Field Explanation required

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

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.

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