-
1. Re: Displaying contract extensions (recruitment industry)
John Powell Aug 30, 2017 5:34 AM (in response to John Powell)Trying to clarify this a bit, in my head it works like this (this is not a correctly written statement, just verbalising what it needs to do):
IF end_date of booking_ref1 < end_date of booking_ref2
THEN "Yes"
ELSE "No"
END
If there's no further booking ref linked to the person on the row then also a no.
I can't see that this can be done
-
2. Re: Displaying contract extensions (recruitment industry)
Jim DehnerAug 30, 2017 2:58 PM (in response to John Powell)
John
Gladly work on
It can be done but I would need to see your workbook (Packaged workbooks and flows: when, why, how ) - but it is a complex viz
Jim
-
3. Re: Displaying contract extensions (recruitment industry)
John Powell Aug 31, 2017 2:21 AM (in response to Jim Dehner)Thanks Jim. Workbook attached.
There's a tab for "All Assignments (by End Date)"
What I need is a marker of some sort to show that although this particular contract is coming to an end that an extension has been made. I can of course see that if I extend the end date filter but that doesn't server the purpose of seeing who's ending this week and marking who has been extended.
-
08. Contract Assignments.twbx 1,023.1 KB
-
-
4. Re: Displaying contract extensions (recruitment industry)
Jim DehnerAug 31, 2017 6:55 AM (in response to John Powell)
Hi John
What - very complex workbook - I am not tracking with you on how you can tell a contract has been extended - I am seeing only "End Date" as dimension and I don't see any flag that would indicate an extension or that the date has been changed - How can you see an extension?
Jim
-
5. Re: Displaying contract extensions (recruitment industry)
John Powell Aug 31, 2017 7:58 AM (in response to Jim Dehner)Hi Jim
I guess you probably can't do this but if you were to extend the date range you're looking at you'll see another row for the extension. That itself would have an Event Ref (dimensions --> Event --> event Ref) that differs from the previous one and has it's own start and end date. Although we don't display the event ref on the report itself as it's meaningless to anyone else by me.
I've attached a screenshot on the assumption you probably can't extend those date ranges in the workbook.
So we can extend the date range to see them but what we use this for is seeing which contractors are finishing up this week and who to contact about new jobs. If we simply extend the date range the report is no longer showing us what we want to see.
Essentially I need to be able to show all the contracts that are due to end this week but highlight the ones that then have a new event ref linked to the same company, job etc. but has a different event ref (which should be a greater number then the current one) AND where the start date is after the end date of the previous.
-
Contract Example.JPG 18.9 KB
-
-
6. Re: Displaying contract extensions (recruitment industry)
Jim DehnerAug 31, 2017 9:41 AM (in response to John Powell)
John I think I have an approach for you but I am having difficulty with the different date formats here in the US
I will walk you through the approach and you can try it and see what it does
The idea here is to use a fixed max and fixed min end date for each unique opportunit
fixed max = {FIXED [Consultant],[Opportunity Ref], [Candidate]:max([End Date])}
(note I used the 3 dimensions only out of ignorance - if 1 will do then just use that)
fixed min = { FIXED [Consultant], [Candidate]:min([End Date])}
And the check =
if ATTR([fixed max end date])>WINDOW_MAX(max([End Date]))
and attr([fixed max end date])<>ATTR([fixed min end date])
then "red" end
things to note -
The Window_Max(Max([end_date]) is to get the max date from your filter
this is a table calculation and it must be set to DOWN
Next you put this on color tile and then edit the color tile as shown
Now it will color the VALUES - red when the condition of the extended date is met
Now here is the problem I keep running into
this is a print of part of a check screen I have been using - the column marked 1 is the Max end date for the permutation created by the formula above
the column marked 2 is the actual date in the sheet - There are 2 rows with the values shown in Red indicating they meet the criteria but if you look at the 2 columns noted above you will see the calculated max date is in 2018 and in no way matches the actual date - I checked to see if there were other records for the permutation but could find none (maybe hidden in the data somewhere?
all the other dates seem to match up OK
That's where I am on this You should be able to add the check and max min dates and highlight the values - but I have not been able to test the data fully
Let me know
Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
-
7. Re: Displaying contract extensions (recruitment industry)
John Powell Aug 31, 2017 9:45 AM (in response to Jim Dehner)1 of 1 people found this helpfulJim that's amazing! Thank you.
The date might actually tie up if that's the max date for the extension. I suspect the others tie up because they have no extension where as those 2 are showing the date of the row you can't see not having full access to our data.
Nearly home time here in the UK so I'll give this a go tomorrow and let you know how I get on.
Thanks again, that's a massive help!
-
8. Re: Displaying contract extensions (recruitment industry)
John Powell Sep 4, 2017 10:00 AM (in response to Jim Dehner)Jim this worked really well! Everything looks to tie up correctly.
Thank you very much for your help.
John
-
9. Re: Displaying contract extensions (recruitment industry)
Jim DehnerSep 4, 2017 3:53 AM (in response to John Powell)
Thanks John
I wondered if it was what you needed - your workbook is so complex it was difficult to tell
Jim