KNOWLEDGE BASE

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


Published: 05 May 2017
Last Modified Date: 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

Answer

  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
Did this article resolve the issue?