# Order & # NO Order by time range condition

[ADSL]

Hi BI Community Team,

I have a table called "Visit Summary by Sales Rep" that it contains the "Time-In" & "Time-Out" when they visit the customer.

> I need help in categorizing the time into groups - early morning, morning, lunch, afternoon, evening & late evening:

If time is from 1:00:00 am to 7:59:59 am = early morning

If time is from 8:00:00 am to 11:59:59 am = morning

If time is from 12:00:00 pm to 13:29:59 pm = lunch

If time is from 13:30:00 pm to 17:29:59 pm = afternoon

If time is from 17:30:00 pm to 20:59:59 pm = evening

If time is from 21:00:00 pm to 23:59:59 pm = late evening

> In these groups, we need to know how many # order, # NO order & customer visited

Any suggestion/advise? Please kindly help.

Thanks and Regards,

[Anonymous · Accepted solution]

Hi @ADSL ,

I suggest you to create a [Time Group] column in your VISIT_SUMM table.

Time Group = 
IF (
    VISIT_SUMM[TIME_IN] = BLANK ()
        || VISIT_SUMM[TIME_OUT] = BLANK (),
    BLANK (),
    SWITCH (
        TRUE (),
        VISIT_SUMM[TIME_IN] >= TIME ( 1, 0, 0 )
            && VISIT_SUMM[TIME_OUT] <= TIME ( 7, 59, 59 ), "Early Morning (1:00:00 am - 7:59:59 am)",
        VISIT_SUMM[TIME_IN] >= TIME ( 8, 00, 00 )
            && VISIT_SUMM[TIME_OUT] <= TIME ( 11, 59, 59 ), "Morning (8:00:00 am - 11:59:59 am)",
        VISIT_SUMM[TIME_IN] >= TIME ( 12, 00, 00 )
            && VISIT_SUMM[TIME_OUT] <= TIME ( 13, 29, 59 ), "Lunch (12:00:00 pm - 13:29:59 pm)",
        VISIT_SUMM[TIME_IN] >= TIME ( 13, 30, 00 )
            && VISIT_SUMM[TIME_OUT] <= TIME ( 17, 29, 59 ), "Afternoon (13:30:00 pm - 17:29:59 pm)",
        VISIT_SUMM[TIME_IN] >= TIME ( 17, 30, 00 )
            && VISIT_SUMM[TIME_OUT] <= TIME ( 20, 59, 59 ), "Evening (17:30:00 pm - 20:59:59 pm)",
        VISIT_SUMM[TIME_IN] >= TIME ( 21, 00, 00 )
            && VISIT_SUMM[TIME_OUT] <= TIME ( 23, 59, 59 ), "Late evening (21:00:00 pm - 23:59:59 pm)"
    )
)

Then create a calculated table.

DimTimeGourp =
DATATABLE (
    "TimeGroup", STRING,
    "GroupSort", INTEGER,
    "TimeIn", STRING,
    "TimeOut", STRING,
    {
        { "Early Morning (1:00:00 am - 7:59:59 am)", 1, "1:00:00 am", "7:59:59 am" },
        { "Morning (8:00:00 am - 11:59:59 am)", 2, "8:00:00 am", "11:59:59 am" },
        { "Lunch (12:00:00 pm - 13:29:59 pm)", 3, "12:00:00 pm", "13:29:59 pm" },
        { "Afternoon (13:30:00 pm - 17:29:59 pm)", 4, "13:30:00 pm", "17:29:59 pm" },
        { "Evening (17:30:00 pm - 20:59:59 pm)", 5, "17:30:00 pm", "20:59:59 pm" },
        { "Late evening (21:00:00 pm - 23:59:59 pm)", 6, "21:00:00 pm", "23:59:59 pm" }
    }
)

Data model:

Measures:

#Order = 
CALCULATE(COUNT(VISIT_SUMM[SALESREP_CODE]),FILTER(VISIT_SUMM,VISIT_SUMM[ORDER_IND] = "E" && VISIT_SUMM[VISIT_IND] = "V" && VISIT_SUMM[CALL_IND] = "1"))

NO Order = 
CALCULATE(COUNT(VISIT_SUMM[SALESREP_CODE]),FILTER(VISIT_SUMM,VISIT_SUMM[ORDER_IND] = BLANK() && VISIT_SUMM[VISIT_IND] = "V" && VISIT_SUMM[CALL_IND] = "1"))

Customer Visited = 
CALCULATE(COUNT(VISIT_SUMM[SALESREP_CODE]),FILTER(VISIT_SUMM,VISIT_SUMM[VISIT_IND] = "V" && VISIT_SUMM[CALL_IND] = "1"))

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ADSL]

Hi @Anonymous ,

Thank you very much for your helpful feedback.

Best Regards,