Oracle Communities

Oracle Analytics and AI

Child Item

How to find previous events

I am working on a requirement to check the / show a date when the last time an applicant applied for a job.

I have an analysis that shows a count of one for a job application and in the last six month so basically if run the report for first of January through June 2018, and they had an application in December 20, 2017, the date column for the last time they had an application should show December 20,2017. any suggestions on how to approach this?

Find more posts tagged with

Accepted answers

All comments

Jerry Casey

Is there more to it than

CASE WHEN "Your 6 Mos. Application Count">0 THEN MAX(Application Date) ELSE cast(null as Date) END?

ForSly

Yes, its not that simple. let me upload snipet of it with an explanation of what I want to see.

ForSly

What I need is on the Last Column date I should only see the first row date (8/9/2016)- Meaning that if I ran the report between from February 2, 2017 job application row count for top row will be zero but the Last Date will be 8/9/2016 since that was the last time they had a 1 outside of the date range

Jerry Casey

I don't fully understand your data or the requirement, but this may work:

My "Delta" column is equivalent to your "Job Applications" column.

By doing a running sum on that column, it provides a basis for

MIN(Archive Date by RSUM). At each "0" in the Delta column

the last column shows the date of the previous "1". "RSUM" can be hidden.

ForSly

This seems closer but how did you create the rsum column? whats the formula?

Jerry Casey

It's just a running aggregate function. RSUM("Job Applications"). If that column is text, you'll have to cast it as a number.

ForSly

Unfortunately its not working as expected, as when I use Archive date as a date filter, for say 02/01/2017 -02/28/2017 if the last time they had a a 1 is say 03/20/2016, that date is not being captured. any suggestions?

Gianni Ceresa

Your model is fairly wrong for the job.

ForSly wrote:Unfortunately its not working as expected, as when I use Archive date as a date filter, for say 02/01/2017 -02/28/2017 if the last time they had a a 1 is say 03/20/2016, that date is not being captured. any suggestions?

A suggestion how to get a record which is explicitly excluded by a filter? Rebuild your model designing it to match your business needs.

It's quite obvious that if you filter on dates between January and February 2017 you can't get values of that same columns outside of that range.

There isn't any other suggestion that remove the filter or change your model to answer your business needs.

Keeping adding workaround on workaround is like a timer on a bomb: maybe not now but at some point it's going to explode.