3 Replies Latest reply on Sep 8, 2017 4:18 AM by Zhouyi Zhang

    Get the date 7 days ago

    Trotte Boman

      Hi all,

       

      I just started using Tableau and I got stuck with using dates. I have a parameter called Date Selection where I can choose any date in 2014-2017 in YEAR-MONTH-DAY format (e.g. 2015-03-09). Then I have a parameter called Date Level which can be set as "Day" , "Week" or "Month". I want to create a new calculated field, Date2, which is different depending on the choice in Date Level;

      If Date Level is "Day", then Date2 is the day before Date Selection (e.g 2015-03-08)

      If Date Level is "Week", then Date2 is the week before Date Selection (e.g 2015-03-02)

      If Date Level is "Month", then Date2 is the month before Date Selection (e.g. 2015-02-09)

       

      Currently, I construct Date2 using

       

      IF [Select Date Level] = 'Month'
      THEN MAKEDATE(DATEPART('year',[Date Selection]),
      DATEPART('month',[Date Selection])-1,
      DATEPART('day',[Date Selection]))
      ELSEIF [Select Date Level] = 'Week'
      THEN MAKEDATE(DATEPART('year',[Date Selection]),
      DATEPART('month',[Date Selection]),
      DATEPART('day',[Date Selection])-7)
      ELSEIF [Select Date Level] = 'Day'
      THEN MAKEDATE(DATEPART('year',[Date Selection]),
      DATEPART('month',[Date Selection]),
      DATEPART('day',[Date Selection])-1)
      END

      but it gets troublesome. Sometimes when I subtract there are no past dates, i.e. if Date Level is "Month" and we are in january, Date2 is null (since it tries to find month 0). Same problem for "Week" if we are in one of the first 7 days of the month, or "Day" if we are on the first day of the month. How can I resolve this, so that if Date Selection is 2015-03-02 and Date Level is "Week", then Date2 is 2015-02-23.