1 Reply Latest reply on May 8, 2018 3:51 PM by Jacob Goffin

    How to filter by last week's data on Friday (week start date), current week's data on all other days

    Matt Lomas

      I'm trying to figure out how to create a date filter that shows last week's data on Fridays and the current week's data on all other day with a few other details

       

      - I changed my date properties to have my week start date be on Friday

      - "Loads" is a calculation based on the total number of delivered loads (no info for today)

       

      I also have the following calculation in my workbook to show loads per day

       

      Loads Per Day

      if datepart('weekday', today()) = 6 THEN ([Loads]/5)

      ELSEIF  datepart('weekday', today()) = 2 THEN ([Loads]/1)

      ELSEIF  datepart('weekday', today()) = 3 THEN  ([Loads]/2)

      ELSEIF  datepart('weekday', today()) = 4 THEN ([Loads]/3)

      ELSEIF  datepart('weekday', today()) = 5 THEN  ([Loads]/4)

      END

       

      I am trying to have my data show all of the previous week's load information on Fridays (at the start of my week) and only show the current week's data for every other day. I was going to restrict my "DeliveryDate" filter to 'this week' but on Friday I am getting no load information (because it's only showing the current week, where there is no delivered load information available). Is there a way I can tell Tableau to only show last week's data on Fridays and this week's data for all other days?

       

      Thanks

        • 1. Re: How to filter by last week's data on Friday (week start date), current week's data on all other days
          Jacob Goffin

          Hi Matt,

           

          One way you could approach this is to create a dynamic filter based on whether today is Friday or not. First we would need a field that identifies if a delivery date is part of the current week or part of last week. We can do that with the DATEDIFF function.

           

          DATEDIFF('week',[Delivery Date],Today())

           

          This calculates the number of "weeks" between today and all your delivery dates. It will return 0 for days that are part of the same week as today, and 1 for days that were in the previous week. If we call that calculation "Weeks Difference" then we can create a second calculation that filters data based on whether it is Friday or not.

           

          IF DATENAME('weekday',Today()) = "Friday"

          THEN [Weeks Difference] = 1

          ELSE [Weeks Difference] = 0

          END

           

          This calculation evaluates differently based on if it's Friday or not, but we will always want to keep the days that evaluate to TRUE. Place this new calculation on the filters shelf, keep TRUE, and then you should have the basis from which you can build your appropriately filtered view!

           

          Hope this helps.

           

          Cheers,

          Jacob

          1 of 1 people found this helpful