topic Re: Set Analysis Expression in QlikView

Set Analysis Expression

Anonymous — Fri, 15 Apr 2016 16:43:17 GMT

Dear Experts,

I am trying to achieve a functionality where if year is selected then all records having year <= that year should be displayed in straight table and count of distinct IDs displayed in text box.

Also if some year is selected and a particular month is selected let say year selected as 2015 and Month selected as April then all records should be displayed till April 2015 (including all records from all months in Year 2014).

Attached is dummy qvw file with dummy data.

Any help in this regard is highly appreciated.

Regards,

Re: Set Analysis Expression

sunny_talwar — Fri, 15 Apr 2016 17:24:43 GMT

Try this:

=Count({<Date = {"$(='<=' & Date(Max(Date)))"}, Year, Month>}ID)

Where Year and Month are created in the script and not on the front end

Re: Set Analysis Expression

swuehl — Fri, 15 Apr 2016 17:24:44 GMT

Maybe like attached (created Year and Month fields in the script):

Re: Set Analysis Expression

swuehl — Fri, 15 Apr 2016 17:28:58 GMT

See also this series of blog posts for an better understanding of analyzing time periods / point in time reporting:

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync‌

P.S. and you don't need the Monthend() function, that's a relict from a different earlier approach.

Re: Set Analysis Expression

Anonymous — Fri, 15 Apr 2016 17:58:09 GMT

Thank you Swuehl.

Solution works perfect and thanks for sharing the blog as it really helped.

Regards,

Re: Set Analysis Expression

Anonymous — Fri, 15 Apr 2016 17:59:20 GMT

Hi Sunny,

Thanks for taking time to reply on my question.

The solution works perfect.

As I can mark only one answer as correct answer I was not able to mark yours as well.

Regards,

Re: Set Analysis Expression

sunny_talwar — Fri, 15 Apr 2016 18:17:47 GMT

You don't have to, but if you really want to you can always mark a helpful answer

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny