topic Re: Using Set Analysis in Table Filters Dimension in App Development

Using Set Analysis in Table Filters Dimension

Anonymous — Fri, 09 Dec 2016 19:55:16 GMT

Hi folks,

I'm having an issue with getting my data to display properly in a table. I'm using the most recent version of Qlik Sense.

This image shows two tables, the one on the left contains dimensions showing my raw data, and the one on the right contains my Month-Year dimension with a measure that contains the expression SUM({<Data_Log.Has_Value={'Y'}>}Data_Log.Row_Count). I want to show in a table the total number "Y" values for each month-year.

For some reason, when I apply this measure to the table on the right, it filters out month-year values that don't have any "Y" values. I feel like I should see April 2005 in the table on the right with a zero value, no?. I clearly have entries for April 2005 in my data model, but it's not present in the table on the right? Why? The dimension in the right table has no limitations.

When I filter on April 2005, this is the result I get:

I'm confused. Any help would be appreciated!!

Re: Using Set Analysis in Table Filters Dimension

marcus_sommer — Sat, 10 Dec 2016 13:26:06 GMT

You could try it with the following: alt(SUM({<Data_Log.Has_Value={'Y'}>}Data_Log.Row_Count), 0)

- Marcus

Re: Using Set Analysis in Table Filters Dimension

Anonymous — Mon, 12 Dec 2016 13:59:47 GMT

Unfortunately that makes no difference... is there a fundamental reason this doesn't work? Seems pretty basic to me.

Re: Using Set Analysis in Table Filters Dimension

sunny_talwar — Mon, 12 Dec 2016 14:04:23 GMT

Have you made sure that 'Show zero values' is checked under Add-Ons -> Data handling?

Re: Using Set Analysis in Table Filters Dimension

Anonymous — Mon, 12 Dec 2016 15:16:43 GMT

Hi Sunny,

The Show zero values is checked. I've attached my QVF... If someone can maybe take a quick look I'd really appreciate it!

Re: Using Set Analysis in Table Filters Dimension

sunny_talwar — Mon, 12 Dec 2016 15:19:51 GMT

May be try this:

Sum({<Data_Log.Has_Value={'Y'}>}[Data_Log.Row_Count]) + Avg({1} 0)

or this:

Sum({<Data_Log.Has_Value={'Y'}>}[Data_Log.Row_Count]) + Avg(0)

Re: Using Set Analysis in Table Filters Dimension

Anonymous — Mon, 12 Dec 2016 15:36:19 GMT

Interesting. This does indeed work. Is this a standard method of forcing Qlik to have a value? Avg(0) is a pretty weird.

Re: Using Set Analysis in Table Filters Dimension

sunny_talwar — Mon, 12 Dec 2016 15:38:54 GMT

Its not a standard way to doing it, but not sure why it did not show up. May be because the data is not null but just missing (not 100% sure). So, just thought of adding Avg(0) which would add 0 to all dimensions even that have missing Data_Log.Has_Value = Y

Re: Using Set Analysis in Table Filters Dimension

Anonymous — Mon, 12 Dec 2016 15:41:22 GMT

Interesting. It's very strange... all dimensions seem to have a valid associated Y/N value, but it's just not working as expected. Anyways, thanks for your help finding a workaround - much appreciated!