1 Reply Latest reply on Nov 27, 2018 5:11 PM by swaroop.gantela

    Day to day comparison based on a variable initial reference point

    Cesar Vega

      I need to do a day to day comparison of data - I.e. day one of this year to day one of last year.  I've read that Tableau is capable of this based on date alone, however my initial point of reference changes from year to year.  The "day enrollment opens" is different each year and I need to do a comparison based on "day X since enrollment opened". 

       

      I.e. in 2017, enrollment opened on 8/1, so "day one" of enrollment" is August 1st, but in 2018 enrollment opened on 8/3, so "day one" of enrollment is August 3rd.

       

      I've gone ahead and the "day of enrollment" calculation in sql, so I have a comparison field I can use - day_of_enrollment - but now I need to dynamically chose the most relevant day_of_enrollment to filter the entire data source.

       

      For example, if the data source is refreshed and we were in the 21st day of enrollment this year, I'd like to compare "day 21" across each year.  If I did max([Day of Enrollment]) that wouldn't work for me, because previous years include up to "day 124" of enrollment.

       

      Expressing the problem in SQL -

       

      select *

       

      from data_set

       

      where day_of_enrollment = (

       

           select day_of_enrollment

       

           from data_set

       

           where date = (

                select max(date) from data_set

           )

      )

       

      Horrible query.  Makes me think that maybe I'm going about this the wrong way.