2 Replies Latest reply on Mar 21, 2018 9:29 AM by Brian Brooks

    Help with calculated field for date filter on date dimension

    Brian Brooks

      Hi Tableau Pros,

      I've got a date calc issue that I'm hoping someone can lend some insight into. I'm working with a specific date dimension which is based on a 4-4-5 calendar for sales and fiscal purposes, but hopefully that is beside the point. The goal here is to provide a Month over Month filter which allows the user to view current year and prior year information based on Month/Period to prior Saturday.

       

      I have a date field that can be used as part of the calculation, and I can derive the prior Saturday's date with a calculated field. I also have a field in the date dimension called "Sales Day in Prd" which is the number of days within the month/period. What I would like to do is find the value for last Saturday (3/17/2018) which is the 21st day of the period. I could then use that value within a calculated field to say "only show me data that falls in Sales Day in Prd values 1-x and in the appropriate Period for all Years".

      month_over_month.png

        • 1. Re: Help with calculated field for date filter on date dimension
          Ivan Young

          Hi Brian,

          So if I am understanding you correctly you want to show records for all the years in the filter with sales days in prod <= 21, or the sales day in prod for the most recent Saturday.  The expression below will create a filter that will return only those rows. Basically we are populating all rows with the Sales Day in Prod from last Saturday.  Let me know if you have any questions.

           

          Regards,

          Ivan

           

          [Sales Day In Prd] <= {MAX(if [Date] = [Prior Saturday] then [Sales Day In Prd] end)}

          1 of 1 people found this helpful
          • 2. Re: Help with calculated field for date filter on date dimension
            Brian Brooks

            Very cool, Ivan! I modified your calculation slightly so that it would also grab the [Sales Prd] value for the Previous Saturday. It now looks like this:

            [Sales Day In Prd] <= {MAX(IF [Date] = [Prior Saturday] then [Sales Day In Prd] end)}

            and

            [Sales Prd] = {MAX(IF [Date]= [Prior Saturday] THEN [Sales Prd] END)}

             

            From what I can tell, it looks like the calculation is doing what I would expect. I'll do more testing on my end but I think this should do the trick. Thanks for the quick solution!