Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

nicklisterman
Contributor

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
MVP
MVP

Re: Show all values but hide outside selection

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

8 Replies

Re: Show all values but hide outside selection

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

nicklisterman
Contributor

Re: Show all values but hide outside selection

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

nicklisterman
Contributor

Re: Show all values but hide outside selection

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

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

nicklisterman
Contributor

Re: Show all values but hide outside selection

See attached.

MVP
MVP

Re: Show all values but hide outside selection

Maybe like

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

nicklisterman
Contributor

Re: Show all values but hide outside selection

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

MVP
MVP

Re: Show all values but hide outside selection

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

Community Browser