Thanks for letting me know.
Thanks for replying.. but I am looking for it to be automatic.. For example.. when I change the filter just to project A.. It should show the calculations by itself and not me to put in manual parameters changes... Can that be done??
Also, can you please share your twbx file to help better?
I need it to be automatic.. because I would be using this on a Dashboard with other things connected to it.. So I cannot add filters to it.. because there are N number of projects and nobody would keep checking for the Start and End Date and then filter it out accordingly.. It has to be automatic..
Please let me know if you need more explanation over it..
Beyond Kelly's response, you are going to have to identify another field that determines the hours worked - looking at the first line of the data, Max worked on Project A on the 1/1/2016 so there does not appear to be anything to differentiate between this row and the work effort from 1/3/2016 onward.
My suggestion would be to either manually set a project reference in the raw data, or you could do that via calculated fields in Tableau - in pseudocode;
IF [Project] = "A"
AND [Date] = "1/3/2017" OR [Date] = "1/4/2017" OR [Date] = "1/1/2017"
THEN "This is project 1"
For additional projects, just keep adding ELSEIF clauses.
Hello Peter, Thanks for replying..
But I am looking for something automatic... I dont wana put in Filters.. For Project A it should calculate hours only built in that date range... I have close to 900 projects in the original data.. I cannot expect end user to Filter in data by Start Date & End Date..
Thanks.. Please let me know if this can be done or cannot be done..
Data blending is not the best way to approach certain problems in Tableau. You will probably need to use data join if you want to achieve your objectives in a more efficient manner.
I can see that you have 2 data sources. My preferred approach will be to join Data source 1 to Data source 2 on [Project] field instead of blending them. This will bring both tables into one view making calculations easier.
However, I've provided a solution using data blending but will advise you to join the data instead.
Step 1: Blend the data on Project
Click on Data, select 'Edit Relationships..'
Step 2: Create calculated field [Project Hours Worked]
ZN(IF ATTR([Date]) >= ATTR([Sheet2 (HoursChange)].[Project Start Date]) AND ATTR([Date]) <= ATTR([Sheet2 (HoursChange)].[Project End Date])
THEN SUM([Hours Worked]) END)
This calculates the number of hours worked during a project date range. However, for some strange reasons, [Date] must be in the view for this to calculate correctly. The following steps below were used to ensure you can display [Project Hours Worked] when [Date] is not on the columns or rows shelf.
Not that there are only 2 qualifying project hours (highlighted in yellow) for max. The third project started on 04/01/2018 while the valid project date range ended on 01/01/2018.
Step 3: Create calculated field [Valid Hours Filter]
[Project Hours Worked] > 0
Use this as a filter set to True. This will filter out non-qualifying project hours.
Step 4: Create calculated field [Row Filter]
LAST() == 0
Step 5: Set up your view as shown below.
Data join instead of blending will save you a lot hassle.
See attached workbook.
Hope this helps.
FilterDate_OO.twbx 19.5 KB