Filter Pane - Displaying Countries for specific sheet , but the choice should be applied for all sheets

[rosemary]

Hi Guys,

I need advice from you.

I have several different sheets in my App with different data source. Each of them has a column which consist a Country.

In each sheet, I need to create a filter pane with a Country - which will be displaying only Countries for specific sheet, but for example if I choose Argentina in Sheet1, it should be applied for all sheets.

Thanks!

[rbartley]

Hi Rosemary,

Yes, there are synchronised keys at the moment since  I don't understand the meaning of the extra fields you have added. If you see my previous response, I asked: 

"I see that you have a date field in each of the Sheet tables (Sheet1, Sheet2 and Sheet3) and an Effective Date in the Bridge and MasterCalendar tables.  What is the relationship between these?  Also  you have included an ID in each of the Sheet tables.  What is the significance of this ID? "

If you can answer these questions, I can decide how best to handle this.  In theory there is nothing wrong with synchronised keys per se, but it is usually recommended to resolve these, for example by using Autonumber to create common key fields.

Richard

[rosemary]

Hi Richard,

So the Bridge table is connecting all Sheet tables with MasterCalendar table. ID is a unique row of each Sheet,  thanks to which the Sheet tables are joined with MasterCalendar table. Effective date is not unique for specific Sheet.

[rosemary]

Hi Richard,

What is more, I have noticed, that if I choose different filter like for example month, the solution is not working.

Rosemary

[rbartley]

Ok, try this (attached) .  

I combined all 3 sheets into one table, with each row showing a value for the Effective Date and Market fields (based on the Sheet1_Market, Sheet2_Market or Sheet3_Market as relevant).  As such, you still retain the common Market field and the field to link to the MasterCalendar table. 

I have modified the variable and chart expressions  as required and it appears to work.

[Ksrinivasan]

hi,

find and revert about attachment, whether it meet your requirement,

ksrinivasan

[rosemary]

Hi,

Please see below:

When I am choosing Argentina from Sheet1 - it is not applying to Sheet2, where Argentina is also available.

[rosemary]

Hi Richard,

But then when I will choose in Sheet1 - country: Argentina and Month: Sep, and move to Sheet2 - Argentina is also visible while it shouldn't - because the month which is assigned to Argentina in Sheet2 is October.

Rosemary

[Ksrinivasan]

hi,

first select country sheet, it shows as you expected,

then select country, wrt chart displayed

all most we reached your requirement.

ksrinivasan

[rbartley]

I unchecked "Include zero values" in the Add-ons/Data handling section of the table on Sheet 2 so that the market doesn't show in the table and changed the definition of the measures.  See the version attached.  The issue with this version is that if you select a month on a sheet where there is no data for this month/market combination, then the Market is removed from the filters.  If this is not ok with you then you will need to alter the load script again to bring in the month of each sheet, e.g. Month(Date_Sheet1) into the Sheets table and then, instead of using the common Month for the filter, use the relevant month field that corresponds to the sheet in question.

The reason that it is so complicated is your requirement to only show the values in the filter that correspond to the data related to that sheet, otherwise you could just use common fields.  This is the way that Qlik is meant to work, where the availability of a value is indicated by the colour coding: white, green, light grey and dark grey.