1 Reply Latest reply on Jul 17, 2012 9:14 AM by Jonathan Drummey

    YTD w/ Changing Time Gap

    Carl Dragseth

      I have a situation where I am comparing last year YTD volumes against this years YTD volumes (aggregated by Month).  Due to accounting issues, there is a latency between the dates we publish our internal volume data.  For example, we publish internal May 2012 numbers on June 15.  And we then Publish June 2012 on July 15.  Unfortunately, this causes an issue with a formula I use to set the YTD check.  Here is the issue:

       

      From June 15-30, there is a 1 month gap between Month(Now()) and the Max month in the data.  Then from July 1 - 15, there is a 2 month gap.  I have tried to create a formula that dynamically looks at the current date Month(Now()) and the Month(Max(GL Date)) and then uses 1 or 2 depending on the difference between the two values.  This errors out because I can't mix aggregated and non-aggregated values in an If-Then.

       

      I would love to create a calculation that Tableau did not see as aggregated, but simply calculated a Integer and that I could use in the formula I have for YTD. Not sure what to do except create a check value in the underlying database tables and use that.

        • 1. Re: YTD w/ Changing Time Gap
          Jonathan Drummey

          Hi Carl,

           

          There are almost always ways around the mixing aggregated/non-aggregated error by finding the right level of aggregation for the non-aggregated fields to turn them into useful aggregations. MIN(), MAX(), and ATTR() are the most common ones used. If you post a packaged workbook with some sample data, I'm pretty sure something can be worked out.