set analysis with different max date for each row

[Ori]

hi everyone,

I have an agent table that shows for each agent when was the last time a record was opened for him/her.

I want to count, considering the dimension (each agent has a different max date) how many records were opened between max date minus 21 days and the max date.

so far i've seen only posts that showed the solution with hard coded dates but this doesn't suit my need because of the varying dates.

this is the measure i was trying to use

count({<opened_at = {">=max(opened_at)-21"}>} sys_id)

example table

agent	max(opened_at)	period start	incident count
1	31/07/2022	10/07/2022
2	15/07/2022	24/06/2022
3	01/07/2022	10/06/2022
4	10/06/2022	20/05/2022
5	01/06/2022	11/05/2022
6	10/06/2022	20/05/2022

thanks!

[sidhiq91]

@Ori  Could you please try something like below expression and let me know if it has worked.

Sum({<max_opened_at={">=$(=Date(Max(max_opened_at)-21,'DD/MM/YYYY'))"}>}sys_id)

If it has resolved your issue, please like and accept it as your solution.

[Ori]

hi @sidhiq91 and thanks for your reply,

I'm afraid your solution will not work because it hard codes maxdate - 21 based on the entire dataset.

what I want to do is take the row context into the equation, calculate the range for each agent and then count the amount of incidents each one had within that range.

[sidhiq91]

@Ori  If I understand correctly what you want is for each Agent we need to find if the difference between Period Start and Max_end_date is greater than 21 days and then count only those Sys_id?

is that right?

[MayilVahanan]

Hi 

Set analysis is calculated based on entire data set. Not by individual rows.

If you want by row (i.e. per Agent wise), then try like below

Dim: Agent

Exp: Count(Aggr(If(opened_at >=Date(Max(opened_at )-21), sys_id), Agent, opened_at))

[Ori]

thank you both for you reply!

@sidhiq91 exactly so

@MayilVahanan I tried the formula you suggested unfortunately that didn't work.

[sidhiq91]

@Ori  I tried something like below. Please let me know if this resolved your issue.

In the backend:

NoConcatenate
Temp:
Load agent,
Date(Date#(max_opened_at,'DD/MM/YYYY'),'DD/MM/YYYY') as max_opened_at,
Date(Date#([period start],'DD/MM/YYYY'),'DD/MM/YYYY') as [period start],
sys_id
Inline [
agent, max_opened_at, period start, sys_id
1, 31/07/2022, 20/07/2022,2
2, 15/07/2022, 24/06/2022,4
3, 01/07/2022, 10/06/2022,6
4, 10/06/2022, 20/05/2022,8
5, 01/06/2022, 17/05/2022,10
6, 10/06/2022, 20/05/2022,12
];

NoConcatenate
Temp1:
Load *,
if(Interval(max_opened_at-[period start],'DD')>=21,'Y','N') as Flag
Resident Temp;
Drop table Temp;

Exit Script;

In the Frontend:

Sum({<Flag={'Y'}>}sys_id)

If this resolves your issue, please like and accept it as a solution.

[Ori]

@sidhiq91 @MayilVahanan thank you both so much.

in the end @MayilVahanan solution was the one (with adjustments).

this is how i adjusted it :

sum(aggr(if(opened_at>=

aggr(date(max(total <caller_id> opened_at)-21,'DD/MM/YYYY'),caller_id,opened_at),1,0)

,caller_id,opened_at))