4 Replies Latest reply on May 16, 2017 8:58 AM by Justin Pulley

    Subjective Filter (not sure how to describe this)

    Justin Pulley

      Let me preface this by saying that in many ways Tableau has made my life easier; then there are people that don't understand that just because tableau looks easy to use it's not a wizard.  You can't just mash up data that has to references to each other and magic them into a visual.  Okay, done with the rant.

       

      My Customer wants data about CapEx projects in a table type dashboard.  Sounds simple right?  well not so much.  One table is called the build_schedule, lots of columns (including Project_Name) and has some of the projects (but not the "special" projects) and date information about when the need by date is (need_by_date), but it also has projects going back years not just this year.  Now, this is a CapEx report, so we then have the MasterCapexList(MCL) table; this table has all of the projects, build ones and specials (Project_Name) from both tables in it and it has some date data, but the date data is not the proper dates to use because of how long before a project is worked it got the money approved (EG. some builds may have had the money approved in mid-2016, but didn't start until this year).

       

      Here is what I am trying to figure out:  Can I create a filter off a calculation that says IF [need_by_date] exists as a column filter to YEAR([need_by_date]) = 2017 otherwise allow the data to show.

       

      I have tried using IF YEAR([need_by_date]) = 2017 OR YEAR([need_by_date) = NULL, but there are two problems with that; 1) it drops the Project_Name(s) found in the 2017_Projects table and 2) apparently there are some future projects in the build_schedule that have a NULL need_by_date.

       

      Now, I also thought about an LOD, but since all of the data is essentially joined on the MasterCapexList using Project_Name, so I don't think an LOD can work.  Any ideas at all would be great.

       

      best I can do is give a table as follows for an idea about data in the tables:

      Project_Name (MCL)Project_Name (build_schedule)need_by_date (build_schedule)Capex $Pending SpendSpent
      ABC123RKE01ABC123RKE012017-01-25500000125000300000
      DEF123RKE01DEF123RKE012017-02-1720000001000000800000
      SP17UPL0175000335002500
      SP17CTL0160000022500050000
      JKL123RKE01JKL123RKE012017-03-051250002500075000
      CM17SPL01250012501000

       

      Message was edited by: Justin Pulley