Set Analysis Expression

[Anonymous]

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,

RG

[sunny_talwar]

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

[swuehl · Accepted solution]

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

[swuehl]

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.

[Anonymous]

Thank you Swuehl.

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

Regards,

RG

[Anonymous]

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,

RG

[sunny_talwar]

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