3 Replies Latest reply on May 24, 2016 1:24 PM by Chris Fromhold

    Discrepancy in Filtering Across Two Data Sources

    Chris Fromhold

      Hello!  I'm trying to create a dashboard that can filter sheets from two data sources by regions that don't match up perfectly.  I have attached a workbook showing a simplified version of the spreadsheets I'm working with, but basically one report has the regions of South, North, East, and West.  The other report has the regions of Eastern, North, South, West, and New Site.  I need to pair the corresponding regions (South with South, Eastern with East, New Site by itself, etc.) so the dashboard will show results from both sources when filtering.  As of now, the parameter I've set can filter by Eastern but leaves out the files from the East because there's not relationship there.  Is this fixed by creating a group, data blending of some sort, or maybe editing the relationships?  I've tried a little bit of everything with no luck.  Thank you in advance for any insight you might have.  

       

       

      Extra Credit: I've figured out how to rank by Top N but have two issues.

      1) When selecting for the Top 2 values to show, the rank will remove the 3rd option even if it's the same value as the 2nd.  For example, the Most Vacancies for all regions is Manager with 3 and then Associate and Coordinator both have 2.  When I apply my rank of Top 2, Coordinator is removed.  I understand the reasoning since I'm saying show me 2, I just can't figure out how to show those equivalent value fields when necessary.

      2) Under the Most Termed Positions, I'd also like it to show the most frequently used Voluntary and Involuntary Termination Reasons for said position.  For example, the Associate field would ideally show the following when looking at all regions:

       

      Title

       

      Voluntary

      Involuntary

      Associate

      31.25%

      Resignation

      Absences

        • 1. Re: Discrepancy in Filtering Across Two Data Sources
          Ivan Young

          Hi Chris,

          I think the easiest way to deal with the Eastern or East issue is to standardize with a calculated field then use that in your Region Parameter filter.

           

          In your Terminations data source create a field called New Region IF [Region] = 'East' THEN 'Eastern' ELSE [Region] END

           

          Update your Region Parameter Calc to use the new field [Region Parameter]=[New Region] OR [Region Parameter]="All"

           

          Try using RANK_DENSE([YTD Terminations] ) for ranking if you want ties included.  Index isn't really the same as rank.

           

          I'm not sure what Most Termed Position is but LODs might be worth looking into.

           

          Good luck,

          Ivan

           

          PS  I would  use East instead of Eastern as the standardized value but you'd need to update your parameter list if you do.

          • 2. Re: Discrepancy in Filtering Across Two Data Sources
            Ashish Chaudhari

            Hi Chris,

             

            Please find the attached screenshot. I am also attaching the workbook but it is in the 9.3 version (can't help).

             

            Data source filter.png

             

            Data source - Open Positions

             

            1. Calculated field Name - Region Parameter

            [Region]=[Parameters].[Region Parameter] OR (if [Parameters].[Region Parameter]="All" then [Region] END)=[Region]

             

            Data source - Terminations (Sample-Terminations)

             

            1. Calculated field Name - Region Parameter Calc

            [Region]=[Region Parameter] or (IF [Region Parameter]="All" then [Region] END)=[Region]

             

            Please edit the filter conditions, you will get the desired output.

             

            Thanks and Regards,

            Ashish Chaudhari

            1 of 1 people found this helpful
            • 3. Re: Discrepancy in Filtering Across Two Data Sources
              Chris Fromhold

              Thank you both so much!  I was able to get each of your ideas to work as desired, thanks again.