Conditional Calculated Fields

Sarah Owens

Summary

Conditional Calculated Fields

Content

Hi,

I come from a Tableau background and am new to Oracle DV and I'm finding it difficult to create calculated fields the way I'm used to. I am trying to create a chart like this:

Headcount by Dept	2017 Q1	2017 Q4	2018 Q1	Year Over Year	Year to Date
Legal	838	832	837	-0.2%	0.6%
HR	12	17	17	41.7%	0.0%
GTO	5,595	6,019	5,125	-8.4%	-14.9%
Sales	19,895	20,324	20,400	2.5%	0.4%
Marketing	417	456	442	6.0%	-3.1%

I can get the counts for 2017 Q1, 2017 Q4 and 2018 Q1. However, I cannot calculate the Year Over Year or the Year to Date correctly. I have tried breaking the YoY into 3 calculations, one for 2017 Q1, one for 2018 Q1 and then the difference. I cannot get the correct counts for 2017 Q1 and 2018 Q1 that will match what is already in the chart. I am using the Quarter Name [Date], Active Headcount [Number] fields. I have a person number available, but could only use that if I ensure I'm counting only the active employees. I have tried every Case statement I can think of to get there using - CASE "Human Resources - Workforce Deployment"."Gregorian Calendar"."Quarter Name" WHEN '2018 Q 1' THEN... and I am not having in luck at getting the correct counts. Also, will have have to have the YoY and YTD calculations in separate charts? Or can I do it all in one? Thanks in advance.

Pathak Sonkar

Hi Sarah,

Are you able to get the counts correct or the counts are incorrect as well?