2 Replies Latest reply on May 9, 2017 4:15 PM by Jamie Burns

    How to 'split' or 'filter' a single dimension twice in same view without excluding any items within the dimension

    Jamie Burns

      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 IDService NameLeave TypeStart Date (Service Name)End Date (Service Name)Days on Leave
      1489HCP2 MWP 1/03/2016 Null
      1489Leave - Social / Respite1 - Respite17/03/2017 4
      1524HCP2 MWP 1/02/2016 Null
      1524Leave - Hospital / Transition Care2 - Hospital6/04/20173/05/201728
      1531HCP4 MWP Dementia 1/02/201619/04/2017Null
      1531Leave - Social / Respite3 - Social23/03/201719/04/20171


      TABLE 2 - Goal


      Client IDHCP Service NameLeave NameLeave TypeLeave Start DateLeave End DateDays on Leave
      1489HCP2 MWPLeave - Social / Respite1 - Respite17/03/2017 4
      1524HCP2 MWPLeave - Hospital / Transition Care2 - Hospital6/04/20173/05/201728
      1531HCP4 MWP DementiaLeave - Social / Respite3 - Social23/03/201719/04/20171




      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.