topic Two tables: All Members and New Members in QlikView

Two tables: All Members and New Members

Wed, 23 Nov 2011 16:57:28 GMT

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.

Two tables: All Members and New Members

swuehl — Wed, 23 Nov 2011 17:46:53 GMT

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

Two tables: All Members and New Members

Wed, 23 Nov 2011 17:57:36 GMT

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).

Two tables: All Members and New Members

swuehl — Wed, 23 Nov 2011 18:02:25 GMT

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

Re: Two tables: All Members and New Members

Wed, 23 Nov 2011 18:13:41 GMT

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!

Re: Two tables: All Members and New Members

swuehl — Wed, 23 Nov 2011 18:48:02 GMT

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

Re: Two tables: All Members and New Members

Wed, 23 Nov 2011 19:46:29 GMT

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

Re: Two tables: All Members and New Members

swuehl — Wed, 23 Nov 2011 22:30:18 GMT

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])

Re: Two tables: All Members and New Members

Mon, 28 Nov 2011 14:42:11 GMT

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

Re: Two tables: All Members and New Members

swuehl — Mon, 28 Nov 2011 17:03:58 GMT

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

Re: Two tables: All Members and New Members

Mon, 28 Nov 2011 17:22:59 GMT

[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.

Re: Two tables: All Members and New Members

swuehl — Mon, 28 Nov 2011 17:33:44 GMT

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.