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

    Anchoring a date parameter boolean relative to today

    Nathan Anderson

      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?