9 Replies Latest reply on Nov 2, 2016 8:57 AM by David Moyer

    Converting calculation on Date back to Timestamp

    david.smith

      After many hours of trying and researching I managed to find a solution that works out if a service call booked on a particular day will have it's SLA window fall on a weekend, and so skip to the Monday following. However, I am hoping someone in the community may be able to help me find the right syntax to add back in the time part of the timestamp field.

       

      I have a timestamp field called "Onsite_deadline" that is labelled below as Onsite SLA. It takes a variety of variables such as time a call is logged, and contracted hours that an engineer will arrive on site (e.g. from 9am to 5.30pm), the SLA window that can be Next Business Day or a fixed period of hours, and then calculates the time and date when the job must have an engineer arrive. In linear mode it will calculate the Onsite SLA typically to fall over a weekend so it needs to have a revision on the date part. However, using the calculation below for the field successfully works out if it needs to skip the weekend and return the right date field, but I am unable to find the right code to strip out the time value from the field before the calculation for the weekend strip out is made, and then concatenate the revised date with weekend calculation built in with the time value into a timestamp field. Right now it returns 00:00 as the time field. Is anyone able to advise how to achieve this?

       

       

      Let ( [

      DaysToAdd =
      Case (
      DayOfWeek ( Onsite_deadline ) = 1; 1; // Sunday
      DayOfWeek ( Onsite_deadline) = 2; 0; // Monday
      DayOfWeek ( Onsite_deadline) = 3; 0; // Tuesday
      DayOfWeek ( Onsite_deadline) = 4; 0; // Wednesday
      DayOfWeek ( Onsite_deadline) = 5; 0; // Thursday
      DayOfWeek ( Onsite_deadline) = 6; 0; // Friday
      DayOfWeek ( Onsite_deadline) = 7; 2)]; // Saturday
      Date ( Month ( Onsite_deadline ); Day ( Onsite_deadline) + DaysToAdd ; Year ( Onsite_deadline) & "" & Time((Onsite_deadline);(Onsite_deadline) ;(Onsite_deadline))
      )
      )

        • 1. Re: Converting calculation on Date back to Timestamp
          David Moyer

          Hi,

          I didn't test this, just typed it out in Notepad ...

          Let (

            DaysToAdd = Case (

              DayOfWeek ( Onsite_deadline ) = 1; 1; // Sunday

              DayOfWeek ( Onsite_deadline ) = 7; 2; // Saturday

              0 // any other day

            );

            Timestamp(

              Date (

                Month ( Onsite_deadline );

                Day ( Onsite_deadline ) + DaysToAdd;

                Year ( Onsite_deadline )

              );

              GetAsTime ( Onsite_deadline )

            )

          )

          1 of 1 people found this helpful
          • 2. Re: Converting calculation on Date back to Timestamp
            david.smith

            David

             

            Thank you so much. This worked straight off the bat.... genius. So grateful you replied.

            • 3. Re: Converting calculation on Date back to Timestamp
              David Moyer

              Dave, you can call me Dave.

              • 4. Re: Converting calculation on Date back to Timestamp
                David Moyer

                By the way, I use you to spread the message ...

                It's better to "Ask a question" than "Start a discussion" on the forum.  The Forum doesn't make this an obvious option - I don't know why.  In my opinion, here's what it should look like:

                Question hope.PNG

                Thanks to all.

                • 6. Re: Converting calculation on Date back to Timestamp
                  David Moyer

                  Hey,

                  just to optimize a bit:

                  Let (

                    [

                      deadline = TO::Onsite_deadline;

                      DaysToAdd = Case (

                        DayOfWeek ( deadline ) = 1; 1; // Sunday

                        DayOfWeek ( deadline ) = 7; 2; // Saturday

                        0 // any other day

                      )

                    ];

                    Timestamp (

                      Date (

                        Month ( deadline );

                        Day ( deadline ) + DaysToAdd;

                        Year ( deadline )

                      );

                      GetAsTime ( deadline )

                    )

                  )

                  • 7. Re: Converting calculation on Date back to Timestamp
                    david.smith

                    HI David

                     

                    Before I reach out to the wider community I thought I might ask you directly seeing as you were amazing in solving my last sticking point in my system. I really hope you don't mind... I am actually building a customer system to dovetail our service operations into HP, and we are one of Europe's largest contract providers for HP - so they are watching our progress with a whole heap of interest as what we have delivered so far has ticked all the boxes of what they would like to achieve for themselves having just bought the Samsung Printing Business. I am ex HP (7 years) and was the UK Marketing Director of Canon and MD of Samsung IT in the UK. I am the hands on CEO of a specialist IT service provider for HP and we do their Government contract work - tenders, deployments, pricing, support, etc. I liaise with HP Palo Alto, Boise and Barcelona for our higher level contact work.

                     

                    I was cheekily hoping you might be able to guide me a little in this bit. Can't promise anything in return although I have talked a lot about you in the right circles. Here is what I was going to post to the community.

                     

                     

                    I am hoping someone might be able to help me with what may be a fairly simple conditional look-up calculation. I am building a billing system that uses start and finish meter readings for assets.

                    The assets all have a unique identifier which is its serial number and this goes into the field “Serial Number”. Each month I run a report that captures start readings at the start of the month and end readings at the end of the month – or the nearest approximate dates when the readings have been taken. The actual start date is a timestamp field that goes into the field “First meter Date” and the end meter date goes into the field “Last Meter Date”.

                     

                    The values are “Mono Pages Start Meter”, “Colour Pages Start Meter”, “Total Pages Start Meter”, “Mono Pages End Meter”, “Colour Pages End Meter”, “Total Pages End Meter”. These values are imported into a table for all assets and a record contains all the metered values at the start and end, plus the serial number of the asset being tracked. The records are pre sorted so that they run by serial number and then descending “last meter date”. The record is given an auto serial number ID in increments of 1 which is called “__pk_meter ID”. In other words for argument’s sake the Jan values for asset “x” have an ID 1, then Feb an ID of 2, etc as I imported all them in one process. There are currently 5,000 assets with meter records for each month for the last few years.

                    I want to be able to cross reference the end meter values of the previous month’s meters (“last meter date”) and use the values in these fields for billing purposes against that serial number in the current month (ie the maths is

                    “Mono Pages End Meter” //of this month

                    -          “mono pages end meter”//of previous month

                    Unless I do this, dependent on the time of day or even the date that the first reading for that month is taken, the billings will miss the lost values between the next month’s start and the previous month’s end if I use the start readings in the calculation – this could be an overnight value or even several days sometimes. Sometimes the system doesn’t take a reading for several weeks and can miss a month or so – so I need to look for the last value recorded in terms of date as it could be more than a month back.

                    How would I cross reference back saying something like assuming we are say in October 2016:

                    Take existing record Asset Value = “serial number”, use it to search all other records with that value, find the record where the “last meter value” is one month back or perhaps more but is the closest date to the current record value, then take all the values corresponding to that date to put them into a new fields in the current record. So I would call the new fields “Mono Bill End Meter”, “Colour Bill End Meter”, “Total Bill End Meter”. I am not sure if a script could perform this on request when I click a button that says “Generate Billing Info”.

                    I can then perform a simple calculation that says new bill = (“Mono Pages End Last Meter” – “ Mono Bill End Meter”)* unit value.

                    Any ideas very gratefully received.

                    • 9. Re: Converting calculation on Date back to Timestamp
                      David Moyer

                      Hi,

                      I’m working out of doors today and won’t be able to look at it until later.

                       

                      cheers, david