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 Spend||Spent|
Message was edited by: Justin Pulley