Forum Discussion

ADSL's avatar
ADSL
Post Prodigy
3 years ago
Solved

# Order & # NO Order by time range condition

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,

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      ADSL
      Post Prodigy
      3 years ago

      Hi Anonymous ,

      Thank you very much for your helpful feedback.

       

      Best Regards,