topic Re: Show all values but hide outside selection in QlikView

Show all values but hide outside selection

Anonymous — Tue, 07 Jun 2016 15:05:06 GMT

I have a pivot table showing two dimensions: Organizations in rows, MonthYear in columns, w/ count of meetings as the expression.

When I select the MonthYear(s) I want to view, the table looks perfect (see below img A). The months with no values remain because other organizations have values in that column. When I select a single organization (img B) the null/missing months disappear. What do I need to do for these to stay? Create a query that populates the missing data?

The one thing I have tried is to enable "Show all values" on the MonthYear but then I get every MonthYear from my Master Calendar (img C). Can I somehow hide the MonthYear(s) that are not selected by the user?

Re: Show all values but hide outside selection

sunny_talwar — Tue, 07 Jun 2016 15:06:39 GMT

Have you tried unchecking 'Suppress When Zero' on the presentation tab?

Re: Show all values but hide outside selection

Anonymous — Tue, 07 Jun 2016 15:08:22 GMT

I currently only have "Populate Missing Cells" checked in the presentation tab.

Re: Show all values but hide outside selection

Anonymous — Tue, 07 Jun 2016 15:10:44 GMT

Updated the original post with one thing I did try and the closest I've been to what I actually want.

Re: Show all values but hide outside selection

sunny_talwar — Tue, 07 Jun 2016 17:04:37 GMT

Would it be possible to share a sample to play around with?

Re: Show all values but hide outside selection

Anonymous — Tue, 07 Jun 2016 17:34:00 GMT

See attached.

Re: Show all values but hide outside selection

swuehl — Tue, 07 Jun 2016 18:03:02 GMT

Maybe like

=If(Count(MD_ID)+sum({<MD_Business_Units>}0)<>0,Count(MD_ID))

Re: Show all values but hide outside selection

Anonymous — Tue, 07 Jun 2016 18:06:35 GMT

Looks like a start but selecting a single organization in that table shows all Organizations and not just the selected

Re: Show all values but hide outside selection

swuehl — Sat, 11 Jun 2016 12:42:58 GMT

Hm, that's not an easy one given the fact that both fields you are selecting in are dimensions of your table.

It should work if you add the missing combinations of MD_Business_Units and MD_MonthYear to the fact table (without adding values for the other fact fields:

TMP:
LOAD DISTINCT Date(MD_MonthYear) as MD_Created_Date
RESIDENT MD_MasterCalendar;

JOIN
LOAD MD_Business_Units
Resident Meeting_Details;

Concatenate (Meeting_Details)
LOAD * Resident TMP;

DROP Table TMP;