How to filter a dimension

[imtiaz_ullah]

Hi guys,

I am having real trouble trying to simply filter a specific value from a dimension I am using inside a pie chart.

I could recreate the dimension from the database but I am using this data elsewhere, so a simple filter within a particular visualisation is all I want to achieve.

All I want to do is filter all records out where a particular column = 1

pseudo code is:

Filter all records where "mobile = 1", but display the 'mobiletype' column as the dimension inside the pie chart

Is it me or is filtering in QS clunky?

Thanks

Immy

[JonnyPoole]

Filtering is usually done by adjusting the 'measure' expression

Say your Dimension is:   MobileType

and your measure is:   sum(Sales)

Adjust the measure expression to be:   sum( {<mobile = {1}>} Sales) 

You can also use if() statements but the above method (SET ANALYSIS) is often cleaner

ex;   sum(  if(mobile=1,Sales) )

[morganaaron]

If you just want this individual chart to be filtered by that field, you can add it using set analysis, or as another option look at using an alternate state.

As an example, your expression would look something like: Sum({<Mobile={1}>} Field)

If you wanted the interactive ability to filter within the chart to a dimension that isn't actually used in the chart, using an alternate state is probably preferable. Create one, set the chart to be in this state and then create a list box and drag it on top of your chart for that functionality - or add it as a dimension if your data allows that.

[imtiaz_ullah]

Hi Guys,

I've tried all your suggestions but it's not working.

All I am trying to do is a COUNT of records, excluding anything with a 1 on the Mobile column.

Just refuses to work

[JonnyPoole]

Can you post your QVF file ?

Its saved under c:\users\<account>\documents\qlik\sense\apps  folder

[Michael_Tarallo]

Hi Imtiaz - can you see if this expression works for you:

This uses Set Analysis - an introduction can be found here:

A Beginners' Introduction to Set Analysis

In regards to your example:

Count({1-$<mobile_column={'1'}>}count_column)

...where mobile_column is the column containing the values you want to filter on and count_column is the column you want to count. The 1-$ will exclude the set of records that = to 1, therefore giving you everything but those records.

Let us know how you do

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

[imtiaz_ullah]

Hi Mike,

Whilst watching your 1.1 whats new video, I stumbled on that Set Analysis video (you should bump it up the list, it's hidden really low) and I am going to try it when I get to work tomorrow.

I followed the video and tried it on my home PC and it worked a treat, so I can only imagine I have something wrong, I think it's missing " around the INT value. I think I made an assumption as it was an INT that it wouldn't need it.

My testing @home proved I do and the video I watched says only dates do not need it.

I'll update tomorrow.

Cheers

Immy

[imtiaz_ullah]

So very odd behaviour, but got it working.

The data set only contains 1's or 0's (1 is mobile, 0 is not).

When I set the filter to be 0 it works fine. When I set it to use 1 it always returned an empty result set.

In the SQL query, I CAST the output column and forced it to be an INT (in the DB it's a BIT!) and then QlikSense was able to filter on the 1.

Is there something odd around handling of BIT values in QS?

This was my working statement in the by the way, which is pretty much what everyone suggested

COUNT({$<IsMobile={"1"}>}Id)

Thanks

Immy