topic Re: Filter Pane - Displaying Countries for specific sheet , but the choice should be applied for all sheets in App Development

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

rosemary — Mon, 20 Dec 2021 21:08:46 GMT

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!

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

Ksrinivasan — Thu, 14 Jan 2021 10:03:30 GMT

hi,

You have to use Chart expression for individual sheet based on country by using Set function,

for Argentina sheet:

Sum({$<Country={'Argentina'}>}[Delivery Qty])

ksrinivasan.

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

rosemary — Thu, 14 Jan 2021 10:20:41 GMT

@Ksrinivasan I am not sure if I am understand you correctly. But the thing is that Argentina was just an example. I have a long list of Countries in each of the tables.

Let's give you an example:

In filter pane, for Sheet1, I am choosing some Country. When I will move to Sheet2 and this specific Country will be available in source data (which is different from data from Sheet1), it should also display the result.

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

rbartley — Thu, 14 Jan 2021 14:34:47 GMT

Hi Rosemary,

Take a look at the attached app. The important things are having a common country field that connects all of the sheets, while also keeping specific country fields.

[Sheet1]: Load * Inline [ Country,Country_Sheet1,ValueSheet1 Austria,Austria,1 Argentina,Argentina,2 Canada,Canada,3 ]; [Sheet2]: NoConcatenate Load * Inline [ Country,Country_Sheet2,ValueSheet2 Belize,Belize,10 Argentina,Argentina,20 USA,USA,30 ];

I have defined 2 sheets, each referring to the relative data table:

So, the first sheet has a filter panel with field Country_Sheet1 and the second sheet's filter panel uses Country_Sheet2. The fact that there is a common field Country, takes care of the filtering of data from both data tables.

As far as the measure is concerned, I defined a variable called vSelectedCountry:

if(GetSelectedCount([Country_Sheet1])>0,GetFieldSelections([Country_Sheet1]), if(GetSelectedCount([Country_Sheet2])>0,GetFieldSelections([Country_Sheet2])) )

I then used this in the definition of the measures in the chart tables on sheets 1 and 2 as follows:

Sheet1:

=Sum({1<[Country_Sheet1]={'$(=$(vSelectedCountry))'}>}[ValueSheet1])

Sheet2:

=Sum({1<[Country_Sheet2]={'$(=$(vSelectedCountry))'}>}[ValueSheet2])

If you select a country for which there is data on both sheets, the appropriate value will be displayed. However,if you select a country for which there is no data on the sheet concerned, it will display a 0.

I hope this helps.

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

rosemary — Mon, 18 Jan 2021 11:43:05 GMT

Hi,

Thanks. But I still have an issue. I have updated your qvf file to reflect the problem. As you can see, when I am uploading data, the loop is detected.

I have created 3 sheets. And also added my filter which is: =if(Country_Sheet2=Market,Market) - and generally it is working but after choosing a country, both dimension Country_Sheet2 and Market appear on the filter bar.

Regards,

Rosemary

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

rbartley — Mon, 18 Jan 2021 17:01:57 GMT

Hi Rosemary,

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?

Richard

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

Ksrinivasan — Mon, 18 Jan 2021 18:39:19 GMT

hi,

just upload qvd file and check your requirement,

ksrinivasan

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

rbartley — Tue, 19 Jan 2021 09:44:50 GMT

Rosemary,

Please take a look at the attached qvf and let me know if this solves your problem. I made a few changes to the load script, which break the loop, and also modified the Market filter and measure on each of the sheets. Now, only one field appears in the selection box.

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

rosemary — Tue, 19 Jan 2021 11:26:25 GMT

Richard,

The output is fine. But after loading the script, 4 sync keys are created.

Is that fine?

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

rosemary — Tue, 19 Jan 2021 11:41:56 GMT

Hi @Ksrinivasan ,

But in your solution, in every sheet, there is a full list of the countries. And the case was to for example in Sheet1 to only display countries for this specific sheet, but additionally when I will moved to Sheet2, the filter should be visible and should work.

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

rbartley — Tue, 19 Jan 2021 11:53:33 GMT

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

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

rosemary — Tue, 19 Jan 2021 12:16:22 GMT

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.

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

rosemary — Tue, 19 Jan 2021 12:40:56 GMT

Hi Richard,

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

Rosemary

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

rbartley — Tue, 19 Jan 2021 13:55:30 GMT

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.

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

Ksrinivasan — Tue, 19 Jan 2021 14:16:21 GMT

hi,

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

ksrinivasan

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

rosemary — Tue, 19 Jan 2021 15:15:31 GMT

Hi,

Please see below:

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

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

rosemary — Tue, 19 Jan 2021 15:24:18 GMT

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

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

Ksrinivasan — Tue, 19 Jan 2021 15:36:52 GMT

hi,

first select country sheet, it shows as you expected,

then select country, wrt chart displayed

all most we reached your requirement.

ksrinivasan

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

rbartley — Tue, 19 Jan 2021 17:33:57 GMT

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.