topic Converting if statement to set analysis in QlikView

Converting if statement to set analysis

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Converting if statement to set analysis

vishsaggi — Fri, 10 Feb 2017 21:54:32 GMT

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)

Re: Converting if statement to set analysis

swuehl — Fri, 10 Feb 2017 22:25:28 GMT

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.

Re: Converting if statement to set analysis

Fri, 10 Feb 2017 22:28:53 GMT

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!

Re: Converting if statement to set analysis

swuehl — Fri, 10 Feb 2017 22:51:33 GMT

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

Re: Converting if statement to set analysis

rubenmarin — Sat, 11 Feb 2017 00:34:23 GMT

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.