Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Waterfall Graph Based On Calculation

mrmmickle1Feb 13 2019 — edited Feb 15 2019

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:

Waterfall Example.jpg

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...):

Waterfall Table.jpg

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'

Data Format for Pivot.jpg

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:

Transfer Sub Query.jpg

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

This post has been answered by mrmmickle1 on Feb 15 2019

Jump to Answer

mrmmickle1

Answer

Got it working by modifying some SQL:

I create a union query that merged Net Other with the other categories:

SET VARIABLE PREFERRED_CURRENCY='Local Currency';SELECT saw_0,saw_1 FROM ((SELECT 
   saw_0,
   SUM(MassiveT.saw_3) saw_1
 FROM (SELECT 
   saw_0,
   saw_1,
   saw_2,
   saw_3
 FROM ((SELECT 
   '1. 2018 Year End HC' saw_0,
   "Department Hierarchy"."SUBLOB Name" saw_1,
   "Basic Information"."Person Number" saw_2,
   1 saw_3
 FROM "Human Resources - Workforce Deployment"
 WHERE 
("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ((cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end))
) UNION (SELECT 
   '4. YTD Hires' saw_0,
   "Department Hierarchy"."SUBLOB Name" saw_1,
   "Basic Information"."Person Number" saw_2,
   1 saw_3
 FROM "Human Resources - Workforce Deployment"
 WHERE 
("Workforce Event Facts"."Employee Hire Count" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" <=  VALUEOF("PREVIOUS_MONTH_OBIA"))
) UNION (SELECT 
   '2. YTD Terminations' saw_0,
   "Department Hierarchy"."SUBLOB Name" saw_1,
   "Basic Information"."Person Number" saw_2,
   -1 saw_3
 FROM "Human Resources - Workforce Deployment"
 WHERE 
(DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND (("Workforce Event Facts"."Employee Voluntary Termination Count" > 0) OR ("Workforce Event Facts"."Employee Involuntary Termination Count" > 0)) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA"))
) UNION (SELECT 
   '3. YTD Transfer Outs' saw_0,
   "Department Hierarchy"."SUBLOB Name" saw_1,
   "Basic Information"."Person Number" saw_2,
   -1 saw_3
 FROM "Human Resources - Workforce Deployment"
 WHERE 
("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN 
 ( 
 SELECT saw_1 FROM (SELECT 
   "Gregorian Calendar"."Month Name" saw_0,
   "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1
 FROM "Human Resources - Workforce Deployment"
 WHERE 
("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA"))
 ) nqw_1 
 )) AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN 
 ( 
 SELECT saw_1 FROM (SELECT 
   "Gregorian Calendar"."Month Name" saw_0,
   "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1
 FROM "Human Resources - Workforce Deployment"
 WHERE 
(DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA")) AND (("Workforce Event Facts"."Employee Voluntary Termination Count" > 0) OR ("Workforce Event Facts"."Employee Involuntary Termination Count" > 0)) AND ("Job"."Job Code" <> 'LTDJBA')
 ) nqw_1 
 )) AND (cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end)
) UNION (SELECT 
   '5. YTD Transfer Ins' saw_0,
   "Department Hierarchy"."SUBLOB Name" saw_1,
   "Basic Information"."Person Number" saw_2,
   1 saw_3
 FROM "Human Resources - Workforce Deployment"
 WHERE 
("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN 
 ( 
 SELECT saw_1 FROM (SELECT 
   "Gregorian Calendar"."Month Name" saw_0,
   "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1
 FROM "Human Resources - Workforce Deployment"
 WHERE 
("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end)
 ) nqw_1 
 )) AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN 
 ( 
 SELECT saw_1 FROM (SELECT 
   "Gregorian Calendar"."Month Name" saw_0,
   "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1
 FROM "Human Resources - Workforce Deployment"
 WHERE 
("Workforce Event Facts"."Employee Hire Count" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" <=  VALUEOF("PREVIOUS_MONTH_OBIA"))
 ) nqw_1 
 )) AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA"))
)) t1 ORDER BY saw_0, saw_1, saw_2, saw_3)MassiveT
 GROUP BY saw_0, saw_1 ORDER BY saw_0)
UNION
(SELECT 
   '6. Net Other' saw_0,
   SUM(Massive.saw_3) saw_1
 FROM (SELECT 
   saw_0,
   saw_1,
   saw_2,
   saw_3
 FROM ((SELECT 
   '1. 2018 Year End HC' saw_0,
   "Department Hierarchy"."SUBLOB Name" saw_1,
   "Basic Information"."Person Number" saw_2,
   -1 saw_3
 FROM "Human Resources - Workforce Deployment"
 WHERE 
("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ((cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end))
) UNION (SELECT 
   '4. YTD Hires' saw_0,
   "Department Hierarchy"."SUBLOB Name" saw_1,
   "Basic Information"."Person Number" saw_2,
   -1 saw_3
 FROM "Human Resources - Workforce Deployment"
 WHERE 
("Workforce Event Facts"."Employee Hire Count" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" <=  VALUEOF("PREVIOUS_MONTH_OBIA"))
) UNION (SELECT 
   '2. YTD Terminations' saw_0,
   "Department Hierarchy"."SUBLOB Name" saw_1,
   "Basic Information"."Person Number" saw_2,
   1 saw_3
 FROM "Human Resources - Workforce Deployment"
 WHERE 
(DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND (("Workforce Event Facts"."Employee Voluntary Termination Count" > 0) OR ("Workforce Event Facts"."Employee Involuntary Termination Count" > 0)) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA"))
) UNION (SELECT 
   '3. YTD Transfer Outs' saw_0,
   "Department Hierarchy"."SUBLOB Name" saw_1,
   "Basic Information"."Person Number" saw_2,
   1 saw_3
 FROM "Human Resources - Workforce Deployment"
 WHERE 
("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN 
 ( 
 SELECT saw_1 FROM (SELECT 
   "Gregorian Calendar"."Month Name" saw_0,
   "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1
 FROM "Human Resources - Workforce Deployment"
 WHERE 
("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA"))
 ) nqw_1 
 )) AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN 
 ( 
 SELECT saw_1 FROM (SELECT 
   "Gregorian Calendar"."Month Name" saw_0,
   "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1
 FROM "Human Resources - Workforce Deployment"
 WHERE 
(DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA")) AND (("Workforce Event Facts"."Employee Voluntary Termination Count" > 0) OR ("Workforce Event Facts"."Employee Involuntary Termination Count" > 0)) AND ("Job"."Job Code" <> 'LTDJBA')
 ) nqw_1 
 )) AND (cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end)
) UNION (SELECT 
   '5. YTD Transfer Ins' saw_0,
   "Department Hierarchy"."SUBLOB Name" saw_1,
   "Basic Information"."Person Number" saw_2,
   -1 saw_3
 FROM "Human Resources - Workforce Deployment"
 WHERE 
("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN 
 ( 
 SELECT saw_1 FROM (SELECT 
   "Gregorian Calendar"."Month Name" saw_0,
   "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1
 FROM "Human Resources - Workforce Deployment"
 WHERE 
("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end)
 ) nqw_1 
 )) AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN 
 ( 
 SELECT saw_1 FROM (SELECT 
   "Gregorian Calendar"."Month Name" saw_0,
   "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1
 FROM "Human Resources - Workforce Deployment"
 WHERE 
("Workforce Event Facts"."Employee Hire Count" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" <=  VALUEOF("PREVIOUS_MONTH_OBIA"))
 ) nqw_1 
 )) AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA"))
) UNION (SELECT 
   '6. Prior Month End HC' saw_0,
   "Department Hierarchy"."SUBLOB Name" saw_1,
   "Basic Information"."Person Number" saw_2,
   1 saw_3
 FROM "Human Resources - Workforce Deployment"
 WHERE 
("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA"))
)) t1 ORDER BY saw_0, saw_1, saw_2, saw_3) Massive
 GROUP BY saw_0, saw_1 ORDER BY saw_0))  t1 ORDER BY saw_0, saw_1

Marked as Answer by mrmmickle1 · Sep 27 2020

1 - 1

Added on Feb 13 2019

#business-intelligence-applications, #business-intelligence-foundation, #business-intelligence-suite-enterprise-edition-obiee

1 comment

145 views

Analytics Software

Waterfall Graph Based On Calculation

Comments

Post Details