Looking for Some Help Understanding Listboxes and Filtering Data

[treborscottnam]

Hello,

I think I've misunderstood a fundamental way QlikView works.

I have a Fact table as follows:

Course	YearTaken
A	2009
B	2010
C	2011
D	2011
E	2012
F	2014

and I connect the YearTaken to my Dimension table:

Year
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015

When I add a listbox using the Year dimension table, it will show all of the years, even though there are no years prior to 2009 and 2013 is missing. Is that the correct way for QlikView to work? 

If I didn't want to include those extra years, what is the best way of excluding them?

I could use an Exists when I load the data, but I wasn't sure that was the best way to approach the issue. I'd rather not use a WHERE statement as the data can change. Is a way in the listbox itself?

As a philosophical question, why would/should these additional fields be shown at all if they are not going to be useful in filtering the data? I understand including 2013, since that might help show the continuity between years and show the year that didn't have data.

Thank you for the help and guidance.

[swuehl]

If you only want to show years with courses, you can use a calculated field in your list box, something like

=Aggr( Only({<Course = {"*"},YearTaken=>}YearTaken), YearTaken)

But what you see is indeed the normal behaviour of QV, if you are using a dimensional table that is not limited to the data used in the fact table.

[dmac1971 · Accepted solution]

Robert you can use an expression list box, basically count a dimension and if greater than 0 show another dimension.  Example included.  If you need anything else will try and help!

[#4]

Hi Robert,

In the listbox, if we use expression and write as:

year(fieldname), it should give only the years that are present

Thanks,

SV

[treborscottnam]

Stefan,

Thank you. This is very helpful.

[treborscottnam]

Hi Dermot,

I really appreciate you taking the time to write out the QVW example for me. I was able to use it in my actual dashboard.

Thank you!

[treborscottnam]

Thank you so much for the response. I really appreciate it.

[dmac1971]

Robert thanks!  Mark the answer as correct and feel free to reach out for further help.  QV is an amazing tool but we all need help!