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.

[swuehl]

Not really sure if I understood correctly, maybe a small data sample and giving the expected result may help.

I would try setting a selection on [Original Year] with [Incurred Year]:

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

But not sure if I got your point.

Regards,

Stefan

[#3]

Another way of saying this: I have two tables, one table I want to vary by just one select box (Incurred Year) and the other table I want to vary by two select boxes (Incurred Year) and (Original Year).

[swuehl]

Hm, what if the user selects different years on both select boxes?

[#5]

It should be set up so you can see 2009 and 2010. You want to see total members in the first table and you want to see only the members that joined in those respective years in the second table.

Thanks for your help on this so far!

[swuehl]

Ok, I think I understand the two table chart.

It seems I still don't understand the impact of those two select boxes. But if you select only one year, but want to have this year impact both the selection state of [Incurred Year] and [Original Year], I would still try something like above

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

(and for the second column, I would add 1 to the set element, like {$(=Only([Incurred Year])+1)}

Maybe this is totally wrong, but I need some more information on your data model and at best some data to work with. If you want, you could upload a small sample app here to the forum (upload available in advanced editor).

Regards,

Stefan

[#7]

This formula works for me:

sum({$<[Incurred Year]={$(=Only([Incurred Year]))},[Acct Orig Eff Date]={"2009*"}>}[Member Count])

It is not dynamic though because 2009 is hard coded in it. I want 2009* to be the Only(Year) that is selected.

It's worth pointing out that Acct Orig Eff Date is in this numeric (non date) format: 20090501 would be May 1 2009.

Stefan - I don't think I can upload part of the model as it is very large

[swuehl · Accepted solution]

It's worth pointing out that Acct Orig Eff Date is in this numeric (non date) format: 20090501 would be May 1 2009.

Stefan - I don't think I can upload part of the model as it is very large

Yeah, that's really worth pointing out.

I would suggest that you parse that [Acct Orig Eff Date] in as a Date, like

Load

...

Date(Date#([Acct Orig Eff Date],'YYYYMMDD')) as [Acct Orig Eff Date],

...

from ...

and may be you want to create an additional YearAcctOrigEffDate also (just use year() function on above created date).

Then an expression like above posted should be quite straight forward.

If this is not possible, try as search expression in your set analysis like:

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

[#9]

Stefan,

I've tried your search expression and various modifications of it. I'm pretty new to set theory so I don't understand all the syntax. It looks like what you have doesn't work because you are trying to match

[Acct Orig Eff Date], which is a YYYYMMDD, with

{"=year(date#([Acct Orig Eff Date],'YYYYMMDD'))=only(Year)"}, which is a YYYY.

I'm hoping there's a way to just change the syntax for this expression. If not, then I can try to change the Load Script. Thanks

[swuehl]

Well my intention was to first parse [Acct Orig Eff Date] in as date type using date#, then apply year to get a year number from that and then to compare this year to only(Year). I assume Year field has a numerical format with format code YYYY also?

Is [Acct Orig Eff Date] maybe a time stamp or something similar, with additional information added after 'YYYYMMDD'? You may need to enter this part into the format code as well, then.

Again, if you could post a small sample of your data (not necessarily your confidential data, but some mock up with identical format), it would probably speed up things.

Regards,

Stefan