A complex set analysis question

[cengizeralp]

Hi everyone,

I want to create a table that returns data between 2 date time ranges. This date time interval will depend on a person's date time value (interval: +/- 1 minute).

Let me explain more clearly what I want to do. One person has many entries. I want to list other people who have passed in front of and after this person in a certain period of time.

For example:

"ID" dimension is seleted as "30216", and "Ent_No" as "Ent_1". 

In this case, below statement creates the table below. This is what I want in case of only one "Ent_No" selected.

count({<ID, Datetime_num={"<$(=Datetime_num+1/24/60) >$(=Datetime_num-1/24/60)"} >} [Name])

ID	Ent_No	Date_time	Datetime_num
38728	Ent 1	19.3.20 17:32	43909,730694
38683	Ent 1	19.3.20 17:32	43909,730764
30015	Ent 1	19.3.20 17:32	43909,730914
20517	Ent 1	19.3.20 17:32	43909,731088
30434	Ent 1	19.3.20 17:32	43909,731204
30735	Ent 1	19.3.20 17:32	43909,731238
30216	Ent 1	19.3.20 17:33	43909,731354
31152	Ent 1	19.3.20 17:33	43909,731620
30142	Ent 1	19.3.20 17:33	43909,731690
30687	Ent 1	19.3.20 17:33	43909,731806
30060	Ent 1	19.3.20 17:34	43909,731979

But If I choose many "Ent_No" or don't select any "Ent_No", this statement does not work. 

I want the table to maintain the same structure for more than one "Ent_No".

Please give me an idea,

Thx in advance,

Cengiz

[cengizeralp]

Hi @sunny_talwar ,

I have found a solution on this issue.

I realized that the problem is caused by the "Datetime_num" value not unique.

I changed my statement as below and the problem solved.

Count({<ID, Card_reader=p({<Datetime_num={"$(=Max(Datetime_num,1))"} >} Card_reader),
              Datetime_num={">=$(=Max(Datetime_num,1)) <$(=Max(Datetime_num,1)+1/24/60)"} >} Datetime_num)
+
Count({<ID, Card_reader=p({<Datetime_num={"$(=Max(Datetime_num,2))"} >} Card_reader),
             Datetime_num={">=$(=Max(Datetime_num,2)) <$(=Max(Datetime_num,2)+1/24/60)"} >} Datetime_num)
+

....

However, as you can see from the expression, I have to rewrite the expression as many as the count of entrance. Unfortunately there are many entries.
How can I solve this simpler. Is there a simpler solution in this regard?

Thx in advance,

Cengiz

[sunny_talwar]

I believe set analysis won't really work here and you will fall back on Aggr() function here

Sum(Aggr(

If(Only({<ID>} Datetime_num) < (Max({<ID>} TOTAL <Ent_No> Datetime_num) + 1/24/60) and Only({<ID>} Datetime_num) > (Max({<ID>} TOTAL <Ent_No> Datetime_num) - 1/24/60), Only({<ID>} [Name]))

, ID, Ent_No, Datetime_num))

[cengizeralp]

Hi Sunny,

It didn't work.

Thank you for your interest.

cengiz

[sunny_talwar]

Can you elaborate on what do you mean it didn't work? Did it give an error or did it give an incorrect output?

[cengizeralp]

It returned only records of selected person.

But my purpose is to list other people who have passed in front of and after this person in a minute.

I think the problem is because there are many doors (Ent_No). The selected person has passage records through different doors. 

[sunny_talwar]

How do you identify person here? Is it the ID field where you make selection? or is it another field called person?

[cengizeralp]

ID: person id number  (Field where we make selection. Person means ID. There is no another field.)

Datetime_num : Entrance date time as number

Card_reader : Gate number (There are many gates)