I will try to explain my issue in as much detail as possible. I appreciate all of the members on this forum for taking the time to review this question. This is a very difficult issue I am facing and I have been trying to solve it for more than a week. I have tried many different attempts with nothing to show for it. It would be absolutely huge if someone cold solve this issue for me. Thank you again in advance for reviewing the below information.
I have an end goal of creating an accurate Waterfall in the OBIEE environment. The issue I am having is that we have a category called Net Other that is a "Plug" in order to make the waterfall tie out. In order to accomplish the correct end result. Net Other contains strange transactions like Deaths and Returns from Leave of Absence etc....
So here is what the correct waterfall looks like with hard coded values:
I can get the numbers I need to use by using a pivot table. Unfortunately, I cannot base a waterfall off of a pivot table from what I gather (Please note in this table the values are not negative for Terms and Transfer Outs, also Net Other is off to the far right because it is a calculated field which is problematic for the correct order I need...):
Data Format to get pivot (Please note Net Other is a Calculated Field in the Pivot Table):
Net Other = '6. Prior Month End HC'+'2. YTD Terminations'+'3. YTD Transfer Outs'-'4. YTD Hires'-'5. YTD Transfer Ins'-'1. 2018 Year End HC'
I can't get all of the values i need in a union query because our Transfer In and Transfer Outs are calculations done with this logic (I need to compare employee id's of multiple queries to get this number):
• Transfer Ins - Employees that are not in the starting headcount roster and are not in the hires roster, but are present in the ending headcount roster.
• Transfer Outs - Employees that are not in the ending headcount roster and are not in the terminations roster, but are present in the starting headcount roster.
The Transfer Logic Looks like this:
In addition I have also tried to use filter to get the fields in column format rather than row format. (see example below)
The problem with this is since the transfer logic is overly complicated I cannot get this into the query (maybe there is a SQL Solution or a way to reference the aggregate result of another query)?
The other issue is for some reason I can't get Transfers to Aggregate. I need the Person Number or Organization Level to be in the query to get the result I need:
For Example:
Note: can Hide Person Number.....but the Count still will not aggregate. Count = "Workforce Deployment Facts"."Employee Headcount" (a measure)
Transfer sub queries look like this:
Currently we accomplish this outside of the system in an access database where I create my on ETL process to transform the data in a format friendly for a stacked column graph. We format the graph by making certain sections transparent .....not sure if this matters but figured it may be relevant :
I'm appreciative for those who have reviewed this post. Thank you again for any help you can provide.
Kind Regards,
-Matt Mickle