Announcements
Meet our latest featured member Bruno! Drop a like and comment. READ ON
cancel
Showing results for
Did you mean:
Contributor III

## Looking for Some Help Understanding Listboxes and Filtering Data

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.

1 Solution

Accepted Solutions
Creator III

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!

7 Replies
MVP

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.

Creator III

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!

Not applicable

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

Contributor III
Author

Stefan,

Thank you. This is very helpful.

Contributor III
Author

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!

Contributor III
Author

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

Creator III

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!

Community Browser