Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Generate Series from today

I'm trying to create a table with 15 minute appointments from today onwards ( or possibly between two dates if it's easier)   I'm using the following to create a table but I'm having trouble filter...
  • PaulDBrown's avatar
    5 years ago

    Can you post a depiction of the expected result? At the moment the code delivers a table with time values, right?

     

    Edit: see if this works for you:

    Appointment Times with date =
    VAR Opening = 9
    VAR Closing = 17
    VAR TimeSlot = 30
    VAR Offset = ( TimeSlot / 2 )
    RETURN
        GENERATE (
            GENERATESERIES ( 0, 1439, 1 ),
            // 24 hours/day * 60 minutes/hour = 1,440 - 1 (initial time 12am) 
            VAR __Minutes = [Value]
            VAR __Time =
                TIME ( 0, __Minutes, 0 ) // MROUND: Rounds Value to the nearest multiple of TimeSlot
            VAR __MinutesRounded =
                MROUND ( ( __Minutes + Offset ), TimeSlot )
            VAR __StartTimeSlotRounded =
                TIME ( 0, __MinutesRounded - TimeSlot, 0 )
            VAR __EndTimeSlotRounded =
                TIME ( 0, __MinutesRounded, 0 )
            VAR TimeValues =
                ROW (
                    "Hour", HOUR ( __Time ),
                    "Minute", MINUTE ( __Time ),
                    "Hour Minute 24h", FORMAT ( __Time, "hh:mm" ),
                    "Hour Minute 12h", FORMAT ( __Time, "hh:mm AM/PM" ),
                    "30 Minute Slot Start", FORMAT ( __StartTimeSlotRounded, "hh:mm" ),
                    "30 Minute Slot End", FORMAT ( __EndTimeSlotRounded, "hh:mm" ),
                    "30 Minute Slot",
                        FORMAT ( __StartTimeSlotRounded, "hh:mm" ) & "-"
                            & FORMAT ( __EndTimeSlotRounded, "hh:mm" ),
                    "Working Hours",
                        IF (
                            ( __Minutes / 60 ) >= Opening
                                && ( __Minutes / 60 ) <= Closing,
                            "Yes",
                            "No"
                        ),
                    "Time", __Time,
                    "TimeSlot Start", __StartTimeSlotRounded,
                    "TimeSlot End", __EndTimeSlotRounded
                )
            VAR Day2 =
                TODAY () + 2
            VAR Calend =
                CALENDAR ( TODAY (), Day2 )
            RETURN
                ADDCOLUMNS (
                    CROSSJOIN ( Calend, TimeValues ),
                    "DateTime",
                        FORMAT ( [Date] & " " & [Hour Minute 24h], "General Date" ),
                    "DateTime Start",
                        FORMAT ( [Date] & " " & [30 Minute Slot Start], "General Date" ),
                    "DateTime End",
                        FORMAT ( [Date] & " " & [30 Minute Slot End], "General Date" )
                )
        )
    

     

    which gets you this (showing the last columns including the date)