2 Replies Latest reply on Feb 1, 2019 11:23 AM by Christopher Cheng

# Anchoring a date parameter boolean relative to today

I'm connected to a client's data source via live SQL. There is a blend that needs to happen because on source is connected to budget target and the other is connected to sale.

I'm trying to track sales relative to budget. The data is updated as of yesterday.

I have a parameter that allows the user to see budget tracking relative to year, quarter, and month using a boolean and the following code:

CASE [Parameters].[Budget Selector]

WHEN "Month"

THEN [Year Id] = {FIXED : max([Year Id])}

AND [Month Id] = {FIXED : max([Month Id])}

WHEN "Year"

THEN [Year Id] = {FIXED : max([Year Id])}

WHEN "Quarter"

THEN  [Year Id] = {FIXED : max([Year Id])}

AND  [Qtr Id] = {FIXED : max([Qtr Id])}

END

SET TO TRUE

Here is the challenge: The client wants to have all the data for 2019 in the budget tracker - this includes dates in the future. The two SQL queries are blended on YearID, QuarterID, and MonthID.

Total sales:

SUM([Orders].[Sales])

Target sales:

SUM([Budget].[Target])

Problem 1:

The above boolean is not working for any level of detail except year.

Solution1:

Anchor the date relative to TODAY()

Problem2:

When TODAY() is active, since the data is current as of yesterday, on the first of the month, when the parameter is set to MONTH, then there is no data.

Solution2:

Anchor relative to today using the following LOD expression:

MAX_MONTH_ID =

{FIXED :

IF DAY(TODAY()) = 1

THEN {FIXED : MONTH(TODAY()) - 1}

ELSE {FIXED : MONTH(TODAY())}

END

Problem3:

Tableau is not reading MONTH(TODAY())-1 as an aggregate expression.

This is probably an easy fix. Can anyone help?

• ###### 1. Re: Anchoring a date parameter boolean relative to today

MAX_MONTH_ID =

{FIXED :

MAX( IF DAY(TODAY()) = 1

THEN {FIXED : MONTH(TODAY()) - 1}

ELSE {FIXED : MONTH(TODAY())}

END)}

1 of 1 people found this helpful
• ###### 2. Re: Anchoring a date parameter boolean relative to today

Wrap it in a MAX function:

{FIXED: MAX(MONTH(TODAY()) - 1)}