Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
kizzydavies
Contributor

How to filter data that applies to an entire sheet

Hi,

I am trying to achieve the following but cannot figure it out (I am using Qlik Sense Desktop).

I would like to create 1 app with 1 data source (an excel spreadsheet with multiple columns of data) which feeds all the sheets on an app but I need each sheet to have its own filters which do not affect any other sheets.

(In other systems you are able to copy the original data, make amendments to the copy and create charts from the copied data.  When updating the original data the copies will automatically update also so multiple data sets are not needed.  I have not been able to replicate this behaviour in Qlik.)

here is a simplified example of what I am trying to achieve.

Sheet 1 - Contains all SALES, IT and HR data.  

Sheet 2 - Contains only HR data BUT the data field that contains the HR information is not part of the charts. 

The charts should only include HR information but the actual dimension would be Location and the measure would be number of employees

Sheet 3 - Contains only SALES data BUT again the SALES filter should not be in the chart.  The charts would only contain Sales data but the dimension would be Location and the measure would be number of stores.

I cannot use the filter panes as this affects all the other sheets.

I have tried using a custom dimension but this only works if you are using that dimension in the chart (I would not be).  I have tried adding it in to an existing chart and the information does not change.

Is there any other simple way of doing this? 

Thanks very much,

Kizzy

23 Replies
ogster1974
Honored Contributor II

Re: How to filter data that applies to an entire sheet

You should be able to achieve this kind of effect using variables and filters using set Analysis attached are a few examples and definitions to help get you started.

Makes Sense to go round in circles

I attach the circles example as this is a working way of showing how you can change the focus of multiple charts on a sheet so your example of Number of employees and number of stores could all be handled in the one chart or multi based on what the users focus is on in your case SALES or HR.

Hope this helps

Regards

Andy

kizzydavies
Contributor

Re: How to filter data that applies to an entire sheet

Hi Andy,

Thanks for the quick response.  Perhaps I should have been more clear.  I am a brand new user who is trialing this system.  The document attached is very detailed but I think is aimed at a more experienced user as some of the information is assumed that you already know it.  Also I am user Qlik Sense Desktop

Please could you clarify the following;

-     How to add an extension

-     design mode - do you mean the left hand options when you edit a sheet - if so I do not have the same options           as you.

Are there any you tube tutorials that are available to show this process?

Thanks very much,

Kizzy

OmarBenSalem
Esteemed Contributor

Re: How to filter data that applies to an entire sheet

Hi Kizzy, Can you please attach your qvf file?

I will try to alter it so that it could achieve your goal

I have to change the script and the expressions in the presentation Once done, I will make it clear for you so you can do it by your self the next time

ogster1974
Honored Contributor II

Re: How to filter data that applies to an entire sheet

Here is where you will find lots of videos on how to start using Qlik Sense.

New to Qlik Sense Videos

In answer to your specifics.

1. Add an extension by going to the the qlik branch site and locating the extension you want in my case it was QSVariable. download the zip file to C:\Users\<your username>\Documents\Qlik\Sense\Extensions\ on your PC and extract it there. That is your extension installed.

2. the misisng icons on the left hand side in design mode im thinking you are using IE Edge which does have that visibility issue try using Firefox or chrome browsers and you should see the desgin icons you need.

Regards

Andy

kizzydavies
Contributor

Re: How to filter data that applies to an entire sheet

Hi Omar,

I am not connected to the server so this file sits on my c drive.

Thanks,

Kizzy

kizzydavies
Contributor

Re: How to filter data that applies to an entire sheet

Hi Andy,


Thanks for the link to the videos.  The link I was sent by Qlik when I registered were all the 'whats new and different updates' and not the basic ones, these are very helpful.  I will go through and also try to add the extensions.

I am using google chrome, so not sure why I am not seeing what you are seeing.

Thanks again,

Kizzy

OmarBenSalem
Esteemed Contributor

Re: How to filter data that applies to an entire sheet

If I fully understand;

You have a dimension called Employees which contains 3 values (Sales, IT and HR) ?
1) In the first sheet, you want to do charts about all 3 types?

2) Only HR

3) Only Sales employees

Is this the way it is?

kizzydavies
Contributor

Re: How to filter data that applies to an entire sheet

Hi omar,

Yes this is a simplified version of what I need.

I actually have about 15 data fields that need to be put into charts and for every sheet the data points have to be filtered by specific data (some will have more than 1 filter).

i.e. sheet 1 - all HR, SALES and IT

sheet 2 - charts only to show HR employees (but the dimension will be location and emasure will be number of employees)

Sheet 3 - charts only to show IT employees who have worked for the company over 1 year.  The chart dimension will be Location and the measure will be number of employees - no where on the chart should you see the filter for IT employees over a year as this point of the charts is to show an analysis of these of employees and not the number of employees itself.

Hope this clarifies it.  I appreciate the help.


Thanks,

Kizzy

OmarBenSalem
Esteemed Contributor

Re: How to filter data that applies to an entire sheet

I think you should work with set analysis:

Let me explain how it works:

1) Let's assume you have Employees = HR, Sales and IT

You want a graphic in which you want to count the total number of employees:

the expression would be : count(Employees)

Now, how to alter this to tell it to count All types of Employees?

=> count(  {<Employees=>}  Employees) : WHICH MEANS count all employees

2) If you want to count only those who are IT:

Count({<Employees={'IT'}>}Employees)

And so on.. hope this helps