Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Show all values but hide outside selection

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?

A.
Screen Shot 2016-06-07 at 10.50.46 AM.png

B.
Screen Shot 2016-06-07 at 10.51.00 AM.png

C.
Screen Shot 2016-06-07 at 11.08.32 AM.png

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

2016-06-11 14_42_01-QlikView x64 - [C__Users_Stefan_Downloads_1056118_3.qvw].png

View solution in original post

8 Replies
sunny_talwar

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

sunny_talwar

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

Anonymous
Not applicable
Author

See attached.

swuehl
MVP
MVP

Maybe like

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

Anonymous
Not applicable
Author

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

swuehl
MVP
MVP

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;

2016-06-11 14_42_01-QlikView x64 - [C__Users_Stefan_Downloads_1056118_3.qvw].png