Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
cengizeralp
Contributor III
Contributor III

A complex set analysis question

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

IDEnt_NoDate_timeDatetime_num
38728Ent 119.3.20 17:3243909,730694
38683Ent 119.3.20 17:3243909,730764
30015Ent 119.3.20 17:3243909,730914
20517Ent 119.3.20 17:3243909,731088
30434Ent 119.3.20 17:3243909,731204
30735Ent 119.3.20 17:3243909,731238
30216Ent 119.3.20 17:3343909,731354
31152Ent 119.3.20 17:3343909,731620
30142Ent 119.3.20 17:3343909,731690
30687Ent 119.3.20 17:3343909,731806
30060Ent 119.3.20 17:3443909,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

Labels (1)
7 Replies
cengizeralp
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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)