Hi All - hoping someone here can help ...
I'm trying to split the 'Service Name' dimension (table 1 below) into 2 separate columns in a single table view ("HCP Service Name" showing only the items starting with 'HCP' in one column, and "Leave Name" showing only the items starting with 'Leave' in another column).
My aim here is to show only the "HCP Service Name" in the view. However I don't want this 'filter' to then remove from the view the data associated only with the "Leave Name" (i.e. the data associated with Service Names beginning with 'Leave') (see tab 2, 'Goal', on the attached Excel file, or see below table 2). I actually only want that data to be what is visible in the view (i.e. 'Leave Type', and the Start Date and End Date to reflect only for the 'Leave Name' column). I don't want these 2 columns to include null values if possible, as I only want to show 'Leave Type', 'Leave Start Date', and 'Leave End Date' for the Service Names beginning with 'Leave'. But I want to see the name of the 'HCP' Service File associated with the same Client ID.
Obviously using the filter function alone will not allow for me to maintain both of these components within the one view pane. I've tried doing this using Sets but am unable to work it out. I'd prefer not to have to have the solution rest on Parameters (if I understand these correctly, since I don't want further selections to have to be made by the end user - I just want the columns to show the info as stated above).
I have attached an Excel file with a small data set (tab 1: 'Data Set') (this might suffice but just in case I've also attached a Tableau twbx with the original data set). For every unique Client ID, there are 2 different 'Service Names' - one starting with 'HCP' and one starting with 'Leave'. There is also for this 'Service Name' a 'Start Date' and 'End Date'.
TABLE 1 - Data set
|Client ID||Service Name||Leave Type||Start Date (Service Name)||End Date (Service Name)||Days on Leave|
|1489||Leave - Social / Respite||1 - Respite||17/03/2017||4|
|1524||Leave - Hospital / Transition Care||2 - Hospital||6/04/2017||3/05/2017||28|
|1531||HCP4 MWP Dementia||1/02/2016||19/04/2017||Null|
|1531||Leave - Social / Respite||3 - Social||23/03/2017||19/04/2017||1|
TABLE 2 - Goal
|Client ID||HCP Service Name||Leave Name||Leave Type||Leave Start Date||Leave End Date||Days on Leave|
|1489||HCP2 MWP||Leave - Social / Respite||1 - Respite||17/03/2017||4|
|1524||HCP2 MWP||Leave - Hospital / Transition Care||2 - Hospital||6/04/2017||3/05/2017||28|
|1531||HCP4 MWP Dementia||Leave - Social / Respite||3 - Social||23/03/2017||19/04/2017||1|
I want to be able to filter the Date range such that I can see the total 'Days on Leave' cumulatively by month. For example, a client's 'Leave Start Date' is in March 2017, and they had 4 days of Social leave during that month, which would show up by filtering the view to March 2017. However they also accumulate 3 more days of Social leave during April 2017, and in filtering the view for April 2017 I'd want to see their total Social leave as 7.