topic Re: GetFieldSelection - Multiple Selection Values in Count(If) in QlikView

GetFieldSelection - Multiple Selection Values in Count(If)

Sun, 17 Mar 2013 18:22:59 GMT

Good Evening

I am trying to create a chart that shows the values based on a Year Selection from my Master Calendar. I am using the formula below

=COUNT(IF(YEAR([CONTRACT END DATE])=GetFieldSelections(Year),1))

This works fine if I only select one value (for example - Year = 2012), however, if I select a number of values, then obviously, it doesn't work.

Is there a formula/adjustment that I can make so that if I select multiple values, I get the correct count?

Thanks in advance.

Re: GetFieldSelection - Multiple Selection Values in Count(If)

swuehl — Sun, 17 Mar 2013 19:32:49 GMT

You could try something like

= Sum(if( Match(Year([CONTRACT END DATE]), $(=GetFieldSelections(Year)) ),1))

which should work, but is not very performant.

If your Year in your master calendar would be linked to CONTRACT END DATE, this would make the task almot trivial (remember you can create multiple master calendar).

If you could create an additional [CONTRACT END YEAR] field from your date in the same table, you can use a simple set expression to calculate your number:

=count({<[CONTRACT END YEAR] = Year>} [CONTRACT END YEAR])

Hope this helps,

Stefan

Re: GetFieldSelection - Multiple Selection Values in Count(If)

Sun, 17 Mar 2013 20:23:07 GMT

Thanks Stefan

I will look into this, to give a bit more insight, what I am trying to do is display on a graph the Joiners and Leavers of a company based on a selection from the Master Calendar (i.e. If I Select 2010, 2011, 2012 & 2013, I want to see the numbers for those who have joined and those who have left). Maybe I'm approaching this from completely the wrong angle? I'm still fairly new to QlikView.

My qvw can be found on the attached link, if it helps.

Thanks

https://www.dropbox.com/s/kbnj53b4glnde10/Date%20Selections%20Test%201.qvw

Re: GetFieldSelection - Multiple Selection Values in Count(If)

swuehl — Sun, 17 Mar 2013 20:35:51 GMT

I think you are pretty much set with your data model, is this your original work already?

For your bar chart expressions, you can use these three:

=count({<DateType = {'Employee'}>} EMPLOYEE_ID)

=count({<DateType = {'End_Date'}>} EMPLOYEE_ID)

=count({<DateType = {'Promo Date'}>} EMPLOYEE_ID)

Re: GetFieldSelection - Multiple Selection Values in Count(If)

Sun, 17 Mar 2013 20:56:00 GMT

Excellent, thanks for assistance on the expressions, this really helps.

The Data Model was assisted by reading through the "QlkiView 11 for Developers" by Miguel Garcia and Barry Harmsen and also a few threads on here, particularly with regards to the MapLink table, I used a qvw that Rob Wunderlich linked to on this thread (http://community.qlik.com/message/320160#320160) and applied it to my data. It was very useful.

Regards

Gareth