Apex Code Development You need to sign in to do that
Don't have an account?
Cluster day(TimeStamp) in different timeslots
Hey experts,
hopefully, somebody will unterstand what i want to do. I am looking for way to divide the business day in different time slots. For each task record, i want to cluster the created date timestamp in one category.
For example:
hh:mm:ss to
08:00:00 to 08:15:00 = part 1
08:15:01 to 08:30:00 = part 2
08:30:01 to 08:45:00 = part 2
08:45:01 to 09:00:00 = part 4
...
17:45:01 to 18:00:00 = part 40
Why ? iI just want to see the productivity development of each sales agent in 15 min ( or 30 min) steps and compare each other. Then, i want to create a real time dashboard which is moving forward over the day.
My first thought was, to create three fields:
The first one, catches the time of the createdDate field:
The second one, transform the value of the first field to a number. ( 08:30:00 == 83000)
The third one, is an if statment to check, in which timeslot the number field falls.
Firstly, this solution is not really nice and secondly it doesn't work, because the if statment exceed the character limit. Of course i can split the if statment in many fields but this would be the worst solution ^^ Any suggestion for this ?
Thanks for your effort guys.
Greetings Marry
hopefully, somebody will unterstand what i want to do. I am looking for way to divide the business day in different time slots. For each task record, i want to cluster the created date timestamp in one category.
For example:
hh:mm:ss to
08:00:00 to 08:15:00 = part 1
08:15:01 to 08:30:00 = part 2
08:30:01 to 08:45:00 = part 2
08:45:01 to 09:00:00 = part 4
...
17:45:01 to 18:00:00 = part 40
Why ? iI just want to see the productivity development of each sales agent in 15 min ( or 30 min) steps and compare each other. Then, i want to create a real time dashboard which is moving forward over the day.
My first thought was, to create three fields:
The first one, catches the time of the createdDate field:
The second one, transform the value of the first field to a number. ( 08:30:00 == 83000)
The third one, is an if statment to check, in which timeslot the number field falls.
Firstly, this solution is not really nice and secondly it doesn't work, because the if statment exceed the character limit. Of course i can split the if statment in many fields but this would be the worst solution ^^ Any suggestion for this ?
Thanks for your effort guys.
Greetings Marry
Each daily task has a created time (minimum: 08:00:00) and an end time (maximum: 18:00:00) and you want to convert this duration in slots of 15 or 30 minutes?
A first try (not satisfactory) could be something like that:
- 08:00:00 is an exception (only slot that doesn't start after the first minute, unhandled in this formula)
- The real big problem is that saved date time are always a time value in Greenwich Mean Time (GMT) and there’s no way to determine a user’s time zone in a formula.
That's why it is 6 * 60 (and not 8 * 60) because Paris is GMT+2 (CEST, saved as local time - 2 hours).So you need a new field for only the time offset that can change with also the daylight saving time.
An accurate formula for an elapse time becomes very complicated because of all these changes that we can not manage with the standard functions of a formula.
All Answers
The following formula doesn't work for the summer-time period of France (returns +1 instead +2)
https://success.salesforce.com/answers?id=9063A000000suLPQAY
This one needs a state: http://www.craigrjordan.com/salesforce/create-a-timezone-formula-in-salesforce/
You enter the risk zone with these ridiculous complicated formulas that are not even correct.
SPLIT, REGEX for formulas fields and TIMEOFFSET are the three main functions missing currently which complicate horribly the formulas.
Each daily task has a created time (minimum: 08:00:00) and an end time (maximum: 18:00:00) and you want to convert this duration in slots of 15 or 30 minutes?
A first try (not satisfactory) could be something like that:
- 08:00:00 is an exception (only slot that doesn't start after the first minute, unhandled in this formula)
- The real big problem is that saved date time are always a time value in Greenwich Mean Time (GMT) and there’s no way to determine a user’s time zone in a formula.
That's why it is 6 * 60 (and not 8 * 60) because Paris is GMT+2 (CEST, saved as local time - 2 hours).So you need a new field for only the time offset that can change with also the daylight saving time.
An accurate formula for an elapse time becomes very complicated because of all these changes that we can not manage with the standard functions of a formula.
I am pretty impressed ! This kind of formula is really smart. The most important thing is the chronological order, so your solution looks sufficient ! Do you have a Blog or sth ?
At least, I am glad that you have a new alternative that could work. I don't have blog but that is a good idea.
For the most complicated problem, there is this page that can help very often: https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=5 ... but even this official page can be obsolete when some functions for formulas appear in a new release. There are many non intuitive tricks difficult to find by ourselves and difficult to prove for all the dates (some of these formulas don't work for all the dates but most of the dates and they are sufficient indeed).
Alain
your approach i will make it ;) Thanks