Converting if statement to set analysis

[#1]

Hi,

How can I convert the following if statement to set analysis expression:

= if ([ReportDate] >=  ServiceStartDate and [ReportDate]< ServiceEndDate,ServiceDescription)

Is this set analysis expression the right way to do it for the above if condition

=aggr(Only({< [ReportDate] = {">=$(date(ServiceStartDate ))<$(date(ServiceEndDate))"} >} ServiceDescription), ServiceDescription)

Thanks!

[vishsaggi]

Seems ok, but were you getting the right results or you are facing any issues?

Try this with slight changes.

= Only({< [ReportDate] = {">=$(= date(ServiceStartDate, 'MM/DD/YYYY')) < $(= date(ServiceEndDate, 'MM/DD/YYYY'))"} >} ServiceDescription)

[swuehl]

Where are you using your original expression, i.e. what is the context of it? And how are these fields related to each other?

I assume you want / need to do a record based comparision, so set analysis is probably not the way to go here.

[#4]

I have a course completion table which has course completion dates by students. I have one more date effective student status table, which has date effective student details(historical data). I need a set analysis or if expression to get historical student information as per the course completion date(i.e.) what was a student's status when he/she completed a course. Yes, it is a record based comparison. Kindly suggest!

[swuehl]

It would be easier to help you if you can provide some sample data and your requested results, best by uploading a small sample QVW and a description of your expected charts.

Uploading a Sample

[rubenmarin]

Hi, as stated by Vishwarath you need to add the '=' sign before the date() function, and maybe adding a format to that date.

If this is a calculated dimension it also need your original aggr():

=aggr(Only({< [ReportDate] = {">=$(=date(ServiceStartDate ))<$(=date(ServiceEndDate))"} >} ServiceDescription), ServiceDescription)

If it's not a calculated dimension, or you want to find a better way of doing it, follow the instuctions given by Stefan.