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!

[MK_QSL]

Can you give one more example as step 3,4 and 5 as I am unable to understand.

[#3]

Hello Manish,

After identifying the Items in step2, here, it is a single item,  item A.

we are to take the type 1 date difference for each item ( user selected date minus posting date of item A of type 1)

i.e., (26-Mar minus 24-Mar) which is 2 days

Now we have to check if we are to count the item A or not, by checking the difference is >1 or not.

Here, since the difference is 2, which is >1, we are to count item A.

Hope this clarifies.

[anbu1984]

Check this

[#5]

Clean!!

This logic should work for the given scenario, whereas.. the record count in real database is quite huge, around 60 lakh(in example, its 6 records) ..so, will the solution work in such cases as well?

And for 27th Mar.. it should eliminate item A, but it shows count as 2. can you explain this further?

Please let me know.

Thanks,

Priya

[#6]

Hii

Try this

Step1 for 26th march

= Concat(DISTINCT If([Posting Date] < Date,Item),',')

Step2

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

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

step3

Count(If(Index(diffr,Item) And Date-[Posting Date] > 1,1))

step4:

Count(If(Index(diffr,Item) And Date-[Posting Date] > 1))

let me know.

[#7]

Hello Navven

Could you please recheck your response.

A()  is not in use as far as i know.. what is that?

And what is diffr?

Thanks!

[anbu1984]

Can you explain how it will eliminate Item A if date selected is 27-Mar?

Use Distinct. You will

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

[#9]

Hello Anbu,

Step1, Step 2 are perfectly fine.

In step 3 : we are to eliminate the records identified in step2 and keep the remaining items.

in step4, for the remaining items as resulted by step 3, we are to calculate the difference of dates and if the difference is >1, we are to calculate the items.

Thanks,

Priya

[anbu1984]

In case of 27-Mar, you will get A in Step 2 and difference (27-Mar Minus 24-Mar) is 3 days which is greater than 1 and count will be 1. Is this expected your result? If yes, then try this expr

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