Set analysis

[#1]

Hello All,

Stuck up with the below scenario. Hope i get help from here and proceed

Dataset:

Item	Posting Date	Type
A	24-Mar	1
B	25-Mar	1
A	25-Mar	2
C	27-Mar	1
C	28-Mar	2
B	29-Mar	2

User selects a date, we are to consider the Items of Type-1, where the posting date is on or before(until) the date selected by user.

Here, we are to make sure that there is no Type 2 happenning for that item, within the date selected by user.

Once we select all such items, we are to calculate the date selected-posting date and see if the difference is >1,

Finally, we are to count such items where the difference is greater than 1.

Eg: User selected date - 26th Mar

step1: Identify Type 1 items within 26th Mar

result:  A,B

step2: Identify if the items have type2 within 26th Mar and remove them, if they have

result: A

step3: find out the date difference, user selected date-posting date

result : 2 days

step4: count the items if the difference is >1,

result: count - 1

The set analysis equivalent of this?

Thanks in advance!

[#2]

Hello Anbu..

This is not working.

We should eliminate the record identified in step2.

Your script is calculating the differnce for the date for the record which is to be excluded.

For 27-Mar, if we add the condition And Type=1, it gives correct result, but the same fails for 29-Mar.

For 28-Mar it should give 2. [Step1: A,B,C , Step2 : A , Step3: (ABC-A = B,C)  , Step4: ( 28 Mar - B date, 28Mar- C date) and count how many have difference >1

[anbu1984 · Accepted solution]

Add minus instead of * in the below expr. Make this change to variable vItm

Concat(DISTINCT {< Item=P({<[Posting Date] ={"<$(=Max(Date))"},Type = {'1'}>})>

-

<Item=P({<[Posting Date] ={"<$(=Max(Date))"},Type = {'2'}>})>}Item,',')

If you want to include where diff is one then use >=

='Step3 & 4: '&Count(If(Index(vItm,Item) And Date-[Posting Date] >= 1 And Type = 1,1))

[#4]

This works.. Thanks for your time!!

Regards,

Priya

[#5]

Hello Anbu

Thi first step where we are using concat, is causing a performance issue

Since we have 30 lakh records.

Could you suggest any alternative for this?

Thanks!

[anbu1984]

=Count(DISTINCT {< Item=P({<[Posting Date] ={"<$(=Max(Date))"},Type = {'1'}>})>

-

<Item=P({<[Posting Date] ={"<$(=Max(Date))"},Type = {'2'}>})>}Item)