Compare Rows of the same field ( Date) in webi

[Former Member]

Hi,

I have a business req where i need to compare multiple rows of the same field ( date field ) and if the days between 1st row and 2nd row is <30 days, i need to show that record. similarly compare 2nd row with 3 row of the same field and if it falls within 30days range show the 2nd row and etc.

have tried using previous function, but we cannot apply filter on previous funtion , since i need to filter out the records that won't fall under 30 days range.

Please find the eg:

Student ID=1, have 3  records for the same services (Ser1) , i need to compare date : 13/1/2016 with 1/1/2016 and if daysbetween them <30 i need to show the record, similarly compare 23/1/2016 with 13/1/2016 for the same student and if it falls <30 days show the record

How do we achieve this in webi ?

Date	Student ID	name	Service	provider
1/1/2016	1	aa	Ser1	p1
13/1/2016	1	aa	Ser1	p1
23/1/2016	1	aa	Ser1	p1
1/1/2016	2	bb	ser2	p2
1/3/2016	3	cc	ser3	p3

Thanks,

-Varaprasad

[Former Member]

Hi Varadprasad,

Could you please try below

create a Dimension as [Test]=If (DaysBetween(Previous([ServiceDate]);[ServiceDate]))+0>30 Then 0 Else 1

And create a filter on the block saying [Test]=1

Regards

Niraj

[Former Member]

Thanks for the quick response Niraj,

This seems to be working, but its taking previous date irrespective of the student ID,

I need to reset this by each student ID.

Thanks,

-Prasad

[Former Member]

Hi,

We can tweak that a bit like below

=(If (DaysBetween(Previous([ServiceDate]);[ServiceDate]))+0>30 Then 0 Else 1) In ([StudentId])

Regards

Niraj

[Former Member]

Hi,

There are student ID's just once, it doesn't have any previous date, what date it will consider during daysbetween() ?

=DaysBetween(Previous([Date]);[Date])In([Student Code])  -- this throwing multivalue error.

Thanks,

-Varaprasad

[Former Member]

We can put a check by taking the count of Student Id and if its only 1 then we will either display or not display based on your requirement.

Can you Try Sum(DaysBetween(Previous([Date]);[Date])In([Student Code]))

[Former Member]

This is throwing same multivalue error and some negative number in few cells.

If there is only on Student Code, what previous date it's referring to ? that's the issue.

Sum(DaysBetween(Previous([Date]);[Date])In([Student Code]))

I think as you said,  we first need to compare date only when student code is more than 1.

Yes we need all the student codes, if student codes are repeating then we need to compare the previous rows date and check if it falls within 30 days.

Thanks,

-Varaprasad

Re: Compare Rows of the same field ( Date) in webi

[Former Member]

We have created a DT with RowNum, can you think off  to use Rownum and make it to work?

ROWID                       Date	Student ID	name	Service	provider
1                  1/1/2016	1	aa	Ser1	p1
2                 13/1/2016	1	aa	Ser1	p1
3                 23/1/2016	1	aa	Ser1	p1
1                 1/1/2016	2	bb	ser2	p2

[Former Member]

Hi Prasad,

Not sure by this way can we just create a Break on Student Id with that way the Student ID's will get split and we can just refer to the original logic that we discussed.

And a little formatting on Break and Table should do.

Regards

Niraj

[Former Member]

I am almost near to what we are looking :

=If ([First].[Row ID]=1 ; 1;If ([First].[Row ID]>1;If (DaysBetween(Previous([First].[dService Date]);[First].[dService Date]))+0>30 Then 0 Else 1))

RowID  Date               StudentCode          Variable(above)   daysbetween

1          10/23/15          1                            1                         

2          4/8/16              1                            0                          168

I have considered the ROW ID since row id gets increment as the same Student Code repeats.

the only issue is if i filter it on variable =1, Still the second row appears where daysbetween is 168.

i don't want that complete student itself.

is there any workout can you think off ?

Thanks

-Varaprasad