Skip to main content
Solved

I am looking to count the number of cases where two activities occur within a short window of time, but running into my aggregation incorrectly counting number of activities.

  • April 13, 2023
  • 3 replies
  • 15 views
M
Level 4

 Imagine an activity table like this:

 

Case 1 - Activity A - 1PM

Case 1 - Activity B - 2PM

Case 1 - Activity C - 3PM

Case 1 - Activity D - 4 PM

 

I want to aggregate on another column.

 

I am using OLAP Table and my KPI is:

 

Name: count of cases when Activity C happens < 2 hours from Activity B

 

Formula:

SUM(

CASE WHEN

HOURS_BETWEEN(

PU_FIRST(Cases,Eventtime,Activity=Activity B),

PU_FIRST(Cases,Eventtime,Activity=Activity C)

) < 2

THEN 1

ELSE 0

END

)

 

When I run this against a list of all cases, I get the right answer 1, but when I aggregate it, I'm getting 4.

 

I thought this would only evaluate once per case... is that incorrect?

 

 

Thanks,

Matt

Best answer by divya.jain12

Can you try this? Not sure if this helps.

 

Count(

distinct CASE WHEN

HOURS_BETWEEN(

PU_FIRST(Cases,Eventtime,Activity=Activity B),

PU_FIRST(Cases,Eventtime,Activity=Activity C)

) < 2

THEN _Case_key

ELSE NULL

END

)

    3 replies

    D
    Level 6
    Forum|alt.badge.img+5

    Hi Matt,

     

    Your formula is correct. When I recreate your formula and put the KPI in an OLAP table and add columns from the case table all is working fine for me.

     

    Could you specify how you are aggregating?

    D
    Level 4
    • divya.jain12
    • Level 4
    • Answer
    • April 14, 2023

    Can you try this? Not sure if this helps.

     

    Count(

    distinct CASE WHEN

    HOURS_BETWEEN(

    PU_FIRST(Cases,Eventtime,Activity=Activity B),

    PU_FIRST(Cases,Eventtime,Activity=Activity C)

    ) < 2

    THEN _Case_key

    ELSE NULL

    END

    )

    M
    Level 4
    • matt.slocu12
    • Author
    • Level 4
    • April 14, 2023

    Hi All, Thank you for the responses. This worked. I also think I know why It wasn't working for me before.

     

    In my OLAP table when I had a dimension as Case (which forced one row for every case), I would get the correct answer of 1.

     

    But when I added in another dimension it would give me a total of activities.

     

    I used dimension = activitytable.state

     

    But I don't think this performs the same as dimension = PU_FIRST(case table,activity table.state)

     

     

    Case - State - Activity - Time

    Case 1 - PA - Activity A - 1PM

    Case 1 - PA - Activity B - 2PM

    Case 1 - PA - Activity C - 3PM

    Case 1 - PA - Activity D - 4 PM

    Community Code of ConductAccessibility statement