KNOWLEDGE BASE

## Find Maximum Daily Sum of Measure When Conditionally Switching Between Date Fields

Published: 05 May 2017

### 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

1. 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.

2. 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.

3. Drag [Maximum value] to Detail on the marks card.
4. Right-click on the axis and select Add Reference Line.
5. 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