Find Maximum Daily Sum of Measure When Conditionally Switching Between Date Fields
Published: 05 May 2017 Last Modified Date: 20 Jul 2023
Question
How to find the maximum daily sum for a measure that is conditionally aggregated up to different date date field.
For example, the central region uses [Order Date] to find daily sales, and the east region uses [Ship Date]. We want to find the highest daily sales for either region.
Environment
Tableau Desktop
Answer
Create a calculated field with a name like Date Switch and a calculation similar to the following:
DATE( IF [Region] = "Central" THEN [Order Date] ELSEIF [Region] = "East" THEN [Ship Date] END)
The above calculation will return a different date field based on the value of [Region]. The DATE() function is used here to truncate up to the day level.
Create a calculated field with a name like Maximum value and a calculation similar to the following:
{ FIXED : MAX( { FIXED [Date Switch], [Region] : SUM( IF [Region] = "Central" OR [Region] = "East" THEN [Sales] END ) } ) }
The above calculation does several things. First, the IF statement returns the [Sales] for only central and east regions. Then we 'fix' the sum of the number of records to every unique value of [Date Switch]. Finally we 'fix' the maximum of all of the daily sums to the data source level.
Drag [Maximum value] to Detail on the marks card.
Right-click on the axis and select Add Reference Line.
In the Edit Reference Line, Band, or Box dialog, do the following, then click OK:
In the Value dropdown, select SUM(Maximum Value)
Format the line as desired
Thank you for providing your feedback on the effectiveness of the article.