Forum Discussion

bikgx's avatar
bikgx
Frequent Visitor
3 years ago
Solved

Count Row with multi condition and multi cut off time

Hi all;

 

i am a beginner but tried to figured out my issue by my self and search on this community but still confused,  i have dataset ( attached here),   https://docs.google.com/spreadsheets/d/1QCzmcZA4p8jHXXPBKLVbqpfgZGbU2mGO/edit?usp=sharing&ouid=100079262241875009342&rtpof=true&sd=true 

 

what i expected is; 

IF

CLIENTt ='AAA', Count picking state='waiting" where Date Created between TODAYDATE(-1) > HOUR 16:30 to TODAYDATE <= HOUR 16:30

IF

CLIENT = 'BBB ', Count picking state= 'waiting' where Date Created between TODAYDATE(-1) > HOUR 13:30 to TODAYDATE <= HOUR 13:30

ELSE 

Count picking state = 'waiting' where Date Created between TODAYDATE(-1) > HOUR 15.00 to TODAYDATE <= HOUR 15.00 

 

note IF TODAY DATE is 11/15/2022 and TODAYDATE(-1) is 11/14/2022 (YESTERDAY)

Result for CLIENT AAA = "6" waiting job

 

what makes me confused is condition count in  Interval Time (multi cut off time) , i tried to get Hour and Minute first and compared it, but it didnt worked

 

Thank you for your help;

Regards;

Azura

 

ClientDate Createdpicking_state
AAA11/15/2022 20:10waiting
AAA11/15/2022 11:09waiting
AAA11/15/2022 10:06waiting
AAA11/15/2022 15:16waiting
AAA11/14/2022 19:56waiting
AAA11/14/2022 15:11waiting
AAA11/14/2022 19:56waiting
AAA11/14/2022 18:11waiting
   
  • Anonymous's avatar
    Anonymous
    3 years ago

    bikgx , please try the following measure:

     

    WaitingJobCount = 
    SUMX(
        'Table1',
        VAR vTime = 
            SWITCH('Table1'[Client]
                ,"AAA", TIME(16, 30, 0)
                ,"BBB", TIME(13, 30, 0)
                ,TIME(15, 0, 0)
            )
        VAR vStartTime = (TODAY()-1) + vTime
        VAR vEndTime = TODAY() + vTime
        VAR vJobWaitingCount =
        IF(
          'Table1'[picking_state] = "waiting"
          && 'Table1'[DateCreated] > vStartTime && 'Table1'[DateCreated] <= vEndTime
          , 1
          , 0
        )
        RETURN vJobWaitingCount
    )

     

    This give me these results below:

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    bikgx , please try the following measure:

     

    WaitingJobCount = 
    SUMX(
        'Table1',
        VAR vTime = 
            SWITCH('Table1'[Client]
                ,"AAA", TIME(16, 30, 0)
                ,"BBB", TIME(13, 30, 0)
                ,TIME(15, 0, 0)
            )
        VAR vStartTime = (TODAY()-1) + vTime
        VAR vEndTime = TODAY() + vTime
        VAR vJobWaitingCount =
        IF(
          'Table1'[picking_state] = "waiting"
          && 'Table1'[DateCreated] > vStartTime && 'Table1'[DateCreated] <= vEndTime
          , 1
          , 0
        )
        RETURN vJobWaitingCount
    )

     

    This give me these results below:

     

     

  • bikgx's avatar
    bikgx
    Frequent Visitor
    3 years ago

    HI Anonymous 

     

    Thank you for your help,  i got your idea and get the result what i expected, 

     

    danke