Not able to comparing date in set analysis

[#1]

Hi,

I have written an expression in set analysis to filter some records.

The given below is the expression i am using :

I am trying to eliminate the records with Action reason in BTF,XTF,ICT,TAF,XFL,XFS and I need to eliminate the records where Transfer In date <Transfer Out date . This is working fine and behaving wierd for Nulls. I am not able to compare null values. I am getting reocrds if transfer in date is null, which i dont need. Is there any work around.

The given below is the query i am using

=Count(

{

$<

[Hire YearMonth] = {"<=$(=MAX(FullMonth))"},

[HADYearMonth] = {"<=$(=Year(MAX(Date + 3)) * 10000 + Month(MAX(Date + 3)) * 100 + Day(MAX(Date + 3)))"}

>

*

(

$<TerminationDateAsNull = {"1"}> +

$<

[TADYearMonth]= {">$(=Year(MAX(Date + 3)) * 10000 + Month(MAX(Date + 3)) * 100 + Day(MAX(Date + 3)))"},

[TerminationYearMonth]={"<=$(=MAX(FullMonth))"}

>

+

$<

[TerminationYearMonth]={">$(=MAX(FullMonth))"}

>

)

- 1 < [TransOutActReason] = {BTF,XTF,ICT,TAF,XFL,XFS}

, [TransInForNullYear]={"<(=TransOutYearMonth)"} --> This is not working >

}

distinct EmplId

Please suggest

Thanks,

Srihari

[#2]

Hi ,

Sorry for the late reply i am on a vacation.

Thanks for the logic. Its working. Now i have a serious issue.

I have multiple records for an employee. iS there a way to figure out the latest record among a set of records , for the selected period, and then apply the current formula over that records . The current record will be max of TransferIndate and TransferOutDate.

Is there a way to acheive this ?

Thanks,

Srihari

[Miguel_Angel_Baeyens]

Hello Srihari,

Check this post and this other post since they describe a question very similar to yours.

Hope that helps.

[#4]

Hi,

I have changed my expression to remove only the records whose transfer out date is less than 2011-apr-01. But i need it to do this dynamically based on current month.

Suppose if the current month is june then i have to take transfer out date is less than 2011-July-01 (First day of next month)

I am thinking of using a variable and assign the value dynamically and then use it ,but i am not able to do after the set analysis ,in the distinct clause (below).

=Count(

{

$<

[Hire YearMonth] = {"<=$(=MAX(FullMonth))"},

[HADYearMonth] = {"<=$(=Year(MAX(Date + 3)) * 10000 + Month(MAX(Date + 3)) * 100 + Day(MAX(Date + 3)))"}

>

*

(

$<TerminationDateAsNull = {"1"}> +

$<

[TADYearMonth]= {">$(=Year(MAX(Date + 3)) * 10000 + Month(MAX(Date + 3)) * 100 + Day(MAX(Date + 3)))"},

[TerminationYearMonth]={"<=$(=MAX(FullMonth))"}

>

+

$<

[TerminationYearMonth]={">$(=MAX(FullMonth))"}

>

)

- 1< [TransOutActReason] = {BTF,XTF,ICT,TAF,XFL,XFS}

,

[TransInYearMonth]={"<(=TransOutYearMonth)"}

>

}

distinct If(Len(TransInYearMonth<0 and Len(TransOutYearMonth)>0 and TransOutYearMonth<20110401 ), EmplId,Null()) --> I need to use the variable here, which is not working.

)

Any suggestion would be great

Thanks,

Srihari

[#5]

Hi Bayens and Chris,

I am posting a new thread on getting the latest record in case of multiple records.

I have attached a sample with the explaination of the requirement.

Please go through and come back if u have a solution.

Thanks,

Srihari