3 Replies Latest reply on Sep 23, 2016 7:34 AM by mohsin.khan.1

    Swapping a 'static date' for a 'calculated date'

    mohsin.khan.1

      Hello,

       

      New here, so apologies if this has been posted before (I did have a search, but couldn't find exactly what I was after). I have a few simple calculated fields:

      TY: MAX([Reporting Date])

      LY: MAX([Reporting Date])-365

       

      And then I'm trying to use those two items to limit by prior year data to run up to the same max period for this year (so that I can have a year-on-year comparison) by using a set of IF statements to classify each row as either '2016 YTD', '2015 YTD', 'Out of Range' - I then figured I could just filter out the 'Out of Range' bucket, and I'd have my sums for 2015 running up to the same day/month as is the case for 2016.

       

      I've tested this out initially by using a very basic IF statement to ensure my rows are being correctly tagged:

      IF [Reporting Date] >= DATE('01/01/2015') AND [Reporting Date] < DATE('01/01/2016') THEN '2015' ELSE 'Out of Range' END

      Date Range Capture.PNG

      I figured I would be able to swap out the DATE('01/01/2016') part for my calculated date:

      IF [Reporting Date] >= DATE('01/01/2015') AND [Reporting Date] < [LY] THEN '2015' ELSE 'Out of Range' END

       

      Only, this results in the "Cannot mix aggregate and non-aggregate arguments with this function" error. My question is, why is this the case? Why can't I simply swap out the static single date for another essentially static single date (that just happens to have been calculated)? This kind of thing would work just fine in Excel. Could someone explain why this doesn't work in Tableau? And how I get around it?

       

      Thanks

        • 1. Re: Swapping a 'static date' for a 'calculated date'
          mohsin.khan.1

          UPDATE:

          I noticed that the MAX([Reporting Date]) function was simply returning the [Reporting Date] per row. What I wanted to see was the MAX value in the entire data set. I found this thread that seems to have the same issue, but didn't really find a solution within it:

          Re: Calculated Field that is max date

           

          Anyone have any idea how to get my calculated field of MAX([Reporting Date]) to give me the same value across all rows?

           

          Thanks

          • 2. Re: Swapping a 'static date' for a 'calculated date'
            Michael Hesser

            Hello Mohsin;

            I'm wondering if your formula to calculate MAX DATE may be resetting itself at each row.

            If this is the case, an LOD calc may be able to help.

             

            Try modifying your formula so it reads:

             

            TY

            {FIXED: MAX([Reporting Date])}

             

            LY

            TY-365

             

            This should convert them to dimensions and get rid of that nasty aggregation mix-up!

            • 3. Re: Swapping a 'static date' for a 'calculated date'
              mohsin.khan.1

              Amazing! Thank you very much Michael! That worked exactly as I wanted.

               

              For further context, I wanted to bring in a bunch of measures and compare them all YoY to form my KPIs at the top of a dashboard - so comparing FY 2015 to YTD 2016 wouldn't have made sense.

               

              Using your FIXED 'fix' calculation, I was able to generate my MAX date, and MAX date minus 365, and then use these in my KPI Date Range calculated field as follows (and then use this field as a filter to only show me '2015 YTD' and '2016 YTD'):

               

              IF [Reporting Date] >= DATE('01/01/2015') AND [Reporting Date] <= [KPI LY] THEN '2015 YTD'

              ELSEIF [Reporting Date] >= DATE('01/01/2016') THEN '2016 YTD'

              ELSE 'Out of Range'

              END

               

              Much appreciated!