11 Replies Latest reply on Nov 12, 2012 8:36 AM by Shawn Wallwork

    Back to filtering Holidays

    Al DeLosSantos

      Hello All,

       

      I'm a Trial user (less than 10 days to solve this problem :^) trying to figure out how to filter holidays in a calculated field.  Here is the ugly starting formula that I use to divide up my current datetime into various periods:

       

      IF DATEPART('weekday', DATETIME(ATTR([Date]))) >= 2 AND DATEPART('weekday', DATETIME(ATTR([Date]))) <= 6 AND

      DATEPART('hour', DATETIME(ATTR([Date]))) >= 8 AND DATEPART('hour', DATETIME(ATTR([Date]))) <= 20

      THEN IIF (DATEPART('hour', DATETIME(ATTR([Date]))) >= 12 AND DATEPART('hour', DATETIME(ATTR([Date]))) <= 17, "On Peak", "Mid Peak")

      ELSE IIF (DATEPART('hour', DATETIME(ATTR([Date]))) >= 0 AND DATEPART('hour', DATETIME(ATTR([Date]))) <= 5, "Super Off", "Off Peak")  END

       

      But, the challenge of filtering out holidays still remains (meaning I've go to treat holidays as an "Off Peak" period if they fall on a weekday).  I've seen several posts on calculating workdays between two dates and I'm trying to learn and implement the SQL solutions I've seen posted (i.e. use a separate table of holiday dates to filter).  However, any tips or existing solutions would be much appreciated.  Test Report is attached if you want to view the table and data sources.

       

      Thanks,

      Al D.

        • 1. Re: Back to filtering Holidays
          Alex Kerin

          Hi Al, Your approach is correct and there are not many other ways around it. I would simplify your datetime(attr([date]) by having a separate calc for that (I couldn't tell why you needed it as you only posted the tbw, not the tbwx (save as)).

           

          I wouldn't mix if and iif unless there's good reason.

          1 of 1 people found this helpful
          • 2. Re: Back to filtering Holidays
            Al DeLosSantos

            Thanks for the quick reply Alex.  I basically started filtering using the same method I had used in Excel (i.e. trying to minimize the if-then statements needed).  So, it sounds like I may need to learn more about manipulating the Tableau environment.  I've attached the packaged workbook for your review.  Thanks for that tip...

            • 3. Re: Back to filtering Holidays
              Alex Kerin

              You don't need a sep calc for date - just tell Tableau it is a date by right clicking, then you can get rid off the datetime(attr([date])

               

              date.png

              • 4. Re: Back to filtering Holidays
                Alex Kerin

                See attached - may help visualize the rules you have in place. I also put some structure around your if statement:

                 

                IF DATEPART('weekday', [Date]) >= 2 AND

                    DATEPART('weekday', [Date]) <= 6 AND

                    DATEPART('hour', [Date]) >= 8 AND

                    DATEPART('hour', [Date])<= 20 THEN

                        IF DATEPART('hour', [Date]) >= 12 AND DATEPART('hour', [Date]) <= 17

                            then "On Peak" else "Mid Peak"

                        END

                ELSE

                    if DATEPART('hour', [Date]) >= 0 AND DATEPART('hour', [Date]) <= 5

                        then "Super Off" else "Off Peak"

                    END

                END

                1 of 1 people found this helpful
                • 5. Re: Back to filtering Holidays
                  Al DeLosSantos

                  Thanks Alex.  Much cleaner looking...and I thought there might be a way to use that If-Then-Else statement instead of the IIF.  Thanks for the help.  You don't know of an easy way to compare fields in two data sources do you (I'm back to checking if a period date falls on a holiday date)?

                  • 6. Re: Back to filtering Holidays
                    Alex Kerin

                    The best way is to join the tables (rather than using blending). I had some difficulties joining the tables to begin with - your date in Ops in Excel are text, not real dates. Your holidays are real dates. I added a column in Excel to give the date in a proper format.

                     

                    Then we can join the tables (right click source, edit tables, add table, Holidays, delete the date to date connection and add my new date (real date) equals holiday date, change to left join (otherwise the data would only have dates that exist in both tables)

                     

                    Now we have the ability to check for Date = Holiday Date in your if statement

                     

                    I changed the date of independence day so that Oct had a holiday...

                    1 of 1 people found this helpful
                    • 7. Re: Back to filtering Holidays
                      Al DeLosSantos

                      Nice Alex...I was trying a similar approach earlier, but missed the fact that the ops date was a text field (Those dates are downloaded from a real-time data historian. My next test hopefully is a direct download via the vendor's ODBC driver which I'm waiting on).

                       

                      Thank you very much for your help on this issue.  I'm testing Tableau against a couple of competitors and so far Tableau's complete solution (application, support, resources) compares very favorably (it is the details that make the difference).

                      Regards,

                      Al D.

                      • 8. Re: Back to filtering Holidays
                        Al DeLosSantos

                        Hello Alex,

                         

                        Not sure if you're still monitoring this thread, but I was wondering if you might be able to comment on Tableau's applicability to a project like mine, from your perspective (i.e. are you working on projects with similar complexity?). Here are additional details that I need to address/consider:

                         

                        1) the calculated field (Period) I created will be unique for numerous operating plants being monitored, so probably lots of permutations using either new calculated field names, or possibly new workbooks (see #4).

                        2) the current time period of the data (October 2012) is a snapshot based on a query performed via excel connection to a real-time data historian (eDNA by InStep).  Ideally I would like to be able to run queries (currently hourly averaged data for a month) directly from Tableau (I was unable to test this functionality in my trial for reasons unrelated to Tableau).

                        3) I still need to add financial calculations to the report, which will be based on plant specific pricing schemes, similar to the period calculations mentioned in #1 above (not sure if I should be using calculated fields or creating new data sources for use in calculations, see #4).

                        4) I'm still trying to think through whether I need to continue to use a method that involves calculated fields in my report, similar to the period field calculated in #1 above.  I wonder if there is a better method, using variables/parameters in created data sources perhaps, that will allow me to implement if/then type control statements (in the calculation of periods, prices and revenues for each of the plants being monitored) based on the data set downloaded via a query.

                         

                        FYI, the basic requirements for this project include:

                         

                        a) ease of data access (Tableau does good here so far).

                        b) ease of data manipulation and calculation (learning how to apply Tableau here)

                        c) clear, insightful presentation of reports (Tableau appears to do well here)

                         

                        Any comments in general will be much appreciated.

                         

                        Best regards,

                        Al D.

                        • 9. Re: Back to filtering Holidays
                          Shawn Wallwork

                          Al, Alex will probably catch this, but if you ever want to 'ping' someone go to the insert link icon (the big A), then go to the second tab 'All Content' and search for the persons name with only 'people' checked. They will usually get sent an email saying they've been mentioned on the forums.

                           

                          So here's a ping for Alex. Let's see if he gets it.

                           

                          --Shawn

                          • 10. Re: Back to filtering Holidays
                            Al DeLosSantos

                            Ah, nice.  Thanks for the tip Shawn...

                            • 11. Re: Back to filtering Holidays
                              Shawn Wallwork

                              To quote Zen Master Jonathan: "You're welcome!"