Loading

Using Level of Detail (LOD) in a CASE or IF Statements Returns Higher Than Expected Results

Publiseringsdato: Apr 13, 2024
Beskrivelse
When using a Level of Detail (LOD) Expression in a CASE or IF statement, the returned results are higher than expected. 

Cause

Level of Detail (LOD) expressions aggregate in a unique way from other calculations and measures within Tableau Desktop. When Tableau Desktop validates a calculated field, the software runs a verification to determine what type of field is being returned, which ultimately will determine how the field is aggregated in the view. 

When the validation check is run (how Tableau Desktop determines if the calculated field is valid or not), Tableau Desktop also determines that the field is not purely a Level of Detail expression, due to the reference to the dimension. Because the field is not validated as a pure Level of Detail expression, the aggregation in the view shows the "unexpected" result. 
Løsning
The following instructions can be reviewed in the attached workbook.

Option 1

Divide the expression within the LOD calculation by SUM( [Number of Records]).
For example: 
CASE [Category]
WHEN "Office Supplies"
THEN { INCLUDE [Customer Name]: MAX([Sales]) }
END

Becomes: 
CASE [Category]
WHEN "Office Supplies"
THEN { INCLUDE [Customer Name]:MAX([Sales])/ SUM([Number of Records])}
END

Option 2

Move the aggregation inside the CASE or IF statement.
For example: 
CASE [Category]
WHEN "Office Supplies"
THEN { INCLUDE [Customer Name]: MAX([Sales]) }
END

Becomes: 
CASE MIN( [Category] )
WHEN "Office Supplies"
THEN SUM( { INCLUDE [Customer Name]:MAX([Sales])} )
END
Flere ressurser


 
Knowledge-artikkelnummer

001473493

Vedlegg

LOD within CASE or IF statement.twbx

1155 KB

 
Laster
Salesforce Help | Article