Level of detail Expressions
Level of detail Expressions
=>Lod's support aggregation at dimensionalities other than view level.
=>You can also attach one or more dimensions to any aggregate expression.
Row level : Expressions referencing unaggregated data source columns are computed for each row.
=>calculates the ratio of profit and sales for every row in the data source, and then sums the numbers.
=>That is, the division is performed before the aggregation.
Ex: [Sales] / [Profit]
cal: profitratio : [Sales] / [Profit]
=>However, this is almost certainly not what you would have intended because summing ratios is generally not useful.
sum(profitratio)
=>Instead, you probably want to know the sum of all profits divided by the sum of all sales.
=>That formula is shown below.
Margin = SUM( [Profit]) / SUM([Sales])
=>In this case, the division is performed after each measure is aggregated.
=>An aggregate calculation allows you to create formulas like this.
=>When you create an aggregate calculation, no further aggregation of the calculation is possible.
=>Therefore, the field’s context menu does not offer any aggregation choices. However, you can disaggregate the field.
=>The rules that apply to aggregate calculations are:
1.For any aggregate calculation, you cannot combine an aggregated value and a disaggregated value.
For example, SUM(Price)*[Items] is not a valid expression because SUM(Price) is aggregated and Items is not.
However, SUM(Price*Items) and SUM(Price)*SUM(Items) are both valid.
2.Constant terms in an expression act as aggregated or disaggregated values as appropriate.
For example: SUM(Price*7) and SUM(Price)*7 are both valid expressions.
3.All of the functions can be evaluated on aggregated values. However, the arguments to any given function must either all be aggregated or all disaggregated.
For example: MAX(SUM(Sales),Profit) is not a valid expression because Sales is aggregated and Profit is not.
However, MAX(SUM(Sales),SUM(Profit)) is a valid expression.
4.The result of an aggregate calculation is always a measure.
5.Like predefined aggregations, aggregate calculations are computed correctly for grand totals.
-----------------------------------------------------
View level : Expressions referencing aggregated data source columns are computed at the dimensionality.
Ex: SUM(Sales) / SUM(Profit) Tableau encloses it in an AGG function:
AGG(SUM(Sales) / SUM(Profit))
---------------------------------------------------
Why you go for lods ? or
How to use aggregate and non-aggregate measures in calculation?
[Sales] – AVG([Sales])
Tableau displays the error message:
“Cannot mix aggregate and non-aggregate arguments with this function”
The user’s intent in this case was to compare store sales for each individual store to the average of sales for all stores.
This can now be accomplished with a level of detail expression: [Sales] - {AVG([Sales])}
--------------------------------------------------
Table-Scoped Level of Detail Expressions:
It is possible to define a level of detail expression at the table level without using any of the scoping keywords.
For example, the following expression returns the minimum (earliest) order date for the entire table:
{MIN([Order Date])} o/p: 1/4/2011
This is equivalent to a FIXED level of detail expression with no dimension declaration:
{FIXED : MIN([Order Date])}
Drag: +year(cal) o/p: 2011 Q1 January 4
--------------------------------------------------
FIXED level of detail expressions:
FIXED level of detail expressions compute a value using the specified dimensions, without reference to the dimensions in the view.
The following FIXED level of detail expression computes the sum of sales per region:
{FIXED [Region] : SUM([Sales])}
This level of detail expression, named [Sales by Region], is then placed on Text to show total sales per region:
Region State Sales by region
Central lllinois 501,240
Indiana 501,240
East connecticut 678,781
The view level of detail is [Region] plus [State], but because FIXED level of detail expressions do not consider the view level of detail, the calculation only uses the [Region] dimension, and so the values for the individual states in each region are identical.
Expression’s granularity:
Coarser : {FIXED [Region] : SUM([Sales])}
Finer : {FIXED [Segment], [Category] : SUM([Sales])}
Ex 1:
Segment Finer
Consumer 387,133.781666667
Corporate 235,382.122266667
Home Office 143,217.716166667
Ex2:
Segment Category Avg. finer
Consumer Furniture 363,952.136000001
Consumer Office supplies 391,049.312000001
Consumer Home Appliances 406,399.897
Ex3:
Segment Category Shipmode Avg. finer
Consumer Furniture First 363,952.136000001
Consumer Furniture Same Day 363,952.136000001
Consumer Furniture Second class 363,952.136000001
Consumer Furniture Standard 363,952.136000001
Note:
sales by region :{FIXED [Region] : SUM([Sales])}
drag into text mark: sum(sales by region)
=>if u change the sum into avg also the result set will not be changed(only for fixed).
=>Level of detail expressions are always automatically wrapped in an aggregate when they are added to a shelf in the view unless they’re used as dimensions. So if you double-click on a shelf and type
{FIXED[Segment], [Category] : SUM([Sales])}
and then press Enter to commit the expression, what you now see on the shelf is
SUM({FIXED[Segment], [Category] : SUM([Sales])})
But if you double-click into the shelf to edit the expression, what you see in edit mode is the original expression.
unless they’re used as dimensions: {fixed :min(order date)}
---------------------------------------------------------------------------------------------------------------------
INCLUDE level of detail expressions :
INCLUDE level of detail expressions compute values using the specified dimensions in addition to whatever dimensions are in the view.
EXCLUDE level of detail expressions :
EXCLUDE level of detail expressions declare dimensions to omit from the view level of detail.
EXCLUDE level of detail expressions are useful for ‘percent of total’ or ‘difference from overall average’ scenarios. They are comparable to such features as Totals and Reference Lines.
EXCLUDE level of detail expression cannot be used in row-level expressions (where there are no dimensions to omit), but can be used to modify either a view level calculation or anything in between (that is, you can use an EXCLUDE calculation to remove dimension from some other level of detail expression).
Ex: The following level of detail expression excludes [Region] from a calculation of the sum of [Sales]:
{EXCLUDE [Region]: SUM([Sales])}
The expression is saved as [ExcludeRegion].
To illustrate how this expression might be useful, first consider the following view, which breaks out the sum of sales by region and by month:
Dropping [ExcludeRegion] on Color shades the view to show total sales by month but without the regional component.
For more examples go through this below links:
https://interworks.com/blog/rcurtis/2016/03/23/tableau-deep-dive-lod-exclude-calculation/
https://interworks.com/blog/rcurtis/2016/03/22/tableau-deep-dive-lod-include-calculation/
No comments