Categories
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?
Answers
-
Is there more to it than
CASE WHEN "Your 6 Mos. Application Count">0 THEN MAX(Application Date) ELSE cast(null as Date) END?
0 -
Yes, its not that simple. let me upload snipet of it with an explanation of what I want to see.
0 -
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
0 -
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.
0 -
This seems closer but how did you create the rsum column? whats the formula?
0 -
It's just a running aggregate function. RSUM("Job Applications"). If that column is text, you'll have to cast it as a number.
0 -
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?
0 -
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.
0
