5 Replies Latest reply on Sep 8, 2016 8:59 AM by Benjamin Greene

    aggregate/constant date in IF statement

    Douglas Sweeney

      I have a single-select filter [LoD] for picking a starting date (DoD, WTD, MTD, etc) to display on a viz, where [MaxDate] is the end of the date range.

       

      What i would like to happen is this;

       

      if [LoD] = "DoD" THEN lookup([MaxDate], -1)               

            ELSEIF [LoD] = "WTD" THEN DATETRUNC('week', [MaxDate], 'Monday')

            ELSEIF [LoD] = "MTD" THEN DATETRUNC('month', [MaxDate])

            ELSEIF [LoD] = "QTD" THEN DATETRUNC("quarter",[MaxDate])

            ELSEIF [LoD] = "YTD" THEN #12/31/2015#

            ELSEIF [LoD] = "Custom" THEN [Manual Start Date]

          END

       

      the "lookup" on DoD causes the usual "all fields must be aggregate or constant...."

       

      1) any ideas how to fix this?  DoD might not always be dateadd('day', -1, [MaxDate]) due to holidays, etc.  I dont want to change everything to attr() because quite a few downstream calculated fields break.  I tried a second custom SQL data source that pulls only the single date value, but since its not in the same data source, it wont work either, apparently.

       

      2) is there any way to tell what type of value a function returns - can i explicitly see if something is returning aggregate or constant?

        • 1. Re: aggregate/constant date in IF statement
          Benjamin Greene

          It is hard to know for sure without more context or a packaged workbook. Could you either mock up the problem in the superstore sample data, or provide more info on the way you are calculating LoD, MaxDate and Manual Start Date, or if any of these are parameters?

          • 2. Re: aggregate/constant date in IF statement
            Douglas Sweeney

            I figured as much - just finished a mock up using SuperStore, <Sheet 15>.

             

            LockFiltMin is the calc field in question.  The way it currently rests, it uses dateadd() to get the previous date, but obviously that doesnt work on Mondays (needs to pull Friday).

             

            If i try lookup(), it will throw the agg/const error.  If i wrap everything in attr() it breaks the [Starting Value], [Arrow], and [Change]

             

            i was going to just put more nested if's in there to subtract 1 day, then 2 days then 3 days until it returns a value, but that gets a little onerous and hoping there were some other ideas.

            • 3. Re: aggregate/constant date in IF statement
              Benjamin Greene

              Gotcha. This is how I imagined it must have been set up, but I wanted to be sure. Try changing the first line of LockFiltMin to the following:

               

              if [LoD] = "DoD" THEN {FIXED : MAX(IF [Order Date]=[MaxDate] THEN NULL ELSE [Order Date] END)}

               

              This should essentially return the max order date that is NOT the max date. In other words, it will give you the second-to-last date in the data (not just the day before the max date), which is what it sounds like you want.

              1 of 1 people found this helpful
              • 4. Re: aggregate/constant date in IF statement
                Douglas Sweeney

                Great solution, thank you!  I also modified the WTD/MTD/QTD to use similar logic;

                 

                ELSEIF [LoD] = "WTD" THEN {FIXED : MIN(IF datepart('week', [MaxDate]) = datepart('week',[Order Date]) and datepart('year', [MaxDate]) = datepart('year', [Order Date]) THEN [Order Date] ELSE NULL END)}

                 

                Out of curiosity, I am a fairly new Tableau user.  Since you commented "This is how I imagined it must have been set up", is there a better way to do what I am trying to show?  At this point, I am just using whatever I can get to work, and still trying to learn the "best practices".

                • 5. Re: aggregate/constant date in IF statement
                  Benjamin Greene

                  I would say your use of parameters and calculated fields here is definitely the best/most efficient way to accomplish what you're going for. I made that comment because, looking at your original posting, I was guessing that LoD and Manual Start Date were probably parameters and MaxDate was probably a level of detail expression that returned the maximum date in the data. The solution I had in mind was based on these assumptions being true, so I just wanted to make sure we were on the same page. As it turns out, we were!