Two tables: All Members and New Members

[#1]

I have two tables. The first table measures all active Members by Area and Incurred Year. Column 1 is for Incurred Year X. Column 2 is for Incurred Year X + 1. The second table is the exact same thing, but it only should measure New Members. In other words, those with an Original Year that matches the Incurred Year.

Right now, to sum member months for Year X, I use this:

sum({$<[Incurred Year]={$(=Only([Incurred Year]))}>}[Member Count])

What do I need to add to make it so [Incurred Year] = [Original Year]

One more complication is that the data doesn't have Original Year, but has an Original Month Year field.

[#2]

[Acct Orig Eff Date] is an integer with the format YYYYMMDD. It's not a timestamp.

Year is numerical with YYYY.

Here's your previous syntax:

=sum({$<[Incurred Year]={$(=Only([Incurred Year]))},[Acct Orig Eff Date]={"=year(date#([Acct Orig Eff Date],'YYYYMMDD'))=only(Incurred Year)"}>}[Member Count])

I think it's saying something like this though, which is causing a problem:

=sum({$<[Incurred Year]={$(=Only([Incurred Year]))},[YYYYMMDD]={"YYYY=YYYY"}>}[Member Count])

I ultimately want to add this criteria "where left([Acct Orig Eff Date] , 4) = Only([Incurred Year])" but I'm not sure the syntax for parsing inside set theory in qlikview.

[swuehl]

I used a search expression like

...[YYYYMMDD]={"=YYYY=YYYY"}

where YYYY=YYYY is more like a conditional, I would indead read it like where Year1=Year2 (both having format code YYYY).

I am still thinking it should work if my assumptions on your data are correct, so it think my assumptions are not correct.

You could step back and try to create the year field from [Acct Orig Eff Date] in the script, then use an expression like posted above.