
1. Re: Calculated Field Explanation required
Abhilash Sharma Oct 20, 2017 3:21 AM (in response to Anuj Chopra)5 of 5 people found this helpfulHi 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 [Sales1] 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, subcategory 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

2. Re: Calculated Field Explanation required
Jim Dehner Oct 20, 2017 6:12 AM (in response to Anuj Chopra)1 of 1 people found this helpfulHi
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

3. Re: Calculated Field Explanation required
Abhilash Sharma Oct 22, 2017 11:30 PM (in response to Jim Dehner)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 Oct 22, 2017 11:51 PM (in response to Abhilash Sharma)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 [Sales1] 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 Oct 23, 2017 1:52 AM (in response to Anuj Chopra)4 of 4 people found this helpfulHi 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