1 Reply Latest reply on Jan 25, 2013 11:12 AM by Catherine Rivier

    Include Date value of Null when prior business day date is selected

    Jackie Klein

      I have a Parameter (Parameter PERIOD shown below) that was created to allow the user to select which Date component to view data by - by Day, Month, Quarter or Year.

       

      What I am trying to do, is if the User selects to view Data by Day, and the Date they select is the prior business day, I want the selection to include both values with the prior business day value as well as Null value.  I have a calculated field for the Date (DATE_FILTER shown below) that I am trying to modify to get it to work this way but not sure how to modify it.  Can you please let me know?  TIA!

       

      Parameter Period.png

      Calc Field DATE_FILTER.png

        • 1. Re: Include Date value of Null when prior business day date is selected
          Catherine Rivier

          Hi Jackie,

          Jonathan Drummey pointed out one piece of this puzzle in the other thread:

          another complexity of your question is how is Tableau to know what is a business day? Is that flagged in the data somehow?

          This is a good point, and the first step of this.  If it's just weekdays, this is just a simple matter of using a datepart function with weekday (where 1=Sunday, 2=Monday, etc.).  And you basically   For example:

               IF DATEPART('weekday',Today()-1)>=2 and DATEPART('weekday',Today()-1)<=6 then Today()-1

               ELSEIF DATEPART('weekday',Today()-2)>=2 and DATEPART('weekday',Today()-2)<=6 then Today()-2

               ELSEIF DATEPART('weekday',Today()-3)>=2 and DATEPART('weekday',Today()-3)<=6 then Today()-3

          else Today()-4

               END

          This new field would have the date that is the previous Business Day, which you can use to match.

           

          If holidays come into play, I've never come up with anything easier than making a list of every holiday date from the years in your data (and into the future), and create a calculated field just like the above flagging these same dates as holidays. Then combine the two.

           

          Then for your specific question:

          What I am trying to do, is if the User selects to view Data by Day, and the Date they select is the prior business day, I want the selection to include both values with the prior business day value as well as Null value.  I have a calculated field for the Date (DATE_FILTER shown below) that I am trying to modify to get it to work this way but not sure how to modify it.  Can you please let me know?  TIA!

           

           

          So if I read this right, someone selects Year and they see the data broken out by Year, Month by Month, and if they select Day, it's broken out by day.  Then they can select a single day, is that right?  So within that single date selection, they either pick a day that is the prior business day to today (so 1/24/2013 for today), or is any other date.  And then you want the specific view you're talking about to occur ONLY if that prior business day is selected?

           

          If I understood that correctly (and please correct me if I didn't), I don't think your work to do this will be in the parameter, but it will be in an entirely new variable.  It will be set up something like:

               IF [Date selected from filter]=[Prior Business Day Date (as created above)] THEN {do thing you want to do}

           

          I'm afraid I don't fully understand what it is you want to do with it, or if I understood your question completely.  If you post more info and (even better) a sample workbook, it should be easy to finish solving.

           

          Hope this starts to help!

          Catherine