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!

[Ksrinivasan]

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.

[rosemary]

@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. 

[rbartley]

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.

[rosemary]

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

[rbartley]

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

[Ksrinivasan]

hi,

just upload qvd file and check your requirement,

ksrinivasan

[rbartley]

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.  

[rosemary]

Richard,

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

Is that fine?

[rosemary]

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.