topic Re: set analysis with different max date for each row in App Development

set analysis with different max date for each row

Ori — Sun, 31 Jul 2022 16:17:06 GMT

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!

Re: set analysis with different max date for each row

sidhiq91 — Sun, 31 Jul 2022 17:24:18 GMT

@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.

Re: set analysis with different max date for each row

Ori — Mon, 01 Aug 2022 05:29:59 GMT

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.

Re: set analysis with different max date for each row

sidhiq91 — Mon, 01 Aug 2022 05:35:42 GMT

@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?

Re: set analysis with different max date for each row

MayilVahanan — Mon, 01 Aug 2022 06:27:57 GMT

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))

Re: set analysis with different max date for each row

Ori — Tue, 02 Aug 2022 11:34:32 GMT

thank you both for you reply!

@sidhiq91 exactly so

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

Re: set analysis with different max date for each row

sidhiq91 — Tue, 02 Aug 2022 11:48:07 GMT

@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.

Re: set analysis with different max date for each row

Ori — Tue, 02 Aug 2022 12:10:56 GMT

@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))