Announcements
cancel
Showing results for
Did you mean:
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])

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

Cengiz

Labels (1)
• ### Set Analysis

7 Replies
Contributor III
Author

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.

Datetime_num={">=\$(=Max(Datetime_num,1)) <\$(=Max(Datetime_num,1)+1/24/60)"} >} Datetime_num)
+
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?

Cengiz

MVP

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

Hi Sunny,

It didn't work.

cengiz

MVP

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

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.

MVP

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

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)