Common 'Period' filter visible for multiple tabs/table

[#1]

Hi all,

In my report I have more than 5 tabs(pages) and each of them displays data from individual QVDs(almost). I have a common area at the top of the report to display the filters (listboxes) . In that , I have to place period related listboxes like Year , Month.

Should I load the period column from all the QVDs with same name (say, Period) and display the year as Year(Period) and month as Month(Period) or I can load periods with names related to tabs (say PeriodForTab1 , PeriodForTab2 , etc) and load the listboxes with respective data visible for each tab alone ? I tried both the ways , in the former way I get some numbers in the tables inside the tabs but in the latter way I am getting different numbers upon selecting same year / month combo. My basic requirement is to have common name for the period.(The former way).

Please pour in your suggestions/ideas.

-James

[Anonymous]

The best way would be to create a Date data island

This means that the field names here must not be linked or associated to other fields in the app.

IE:

// This is your common table

DateIsland:

load Year as DI_Year,

        Month as DI_Month,

       Week as DI_Week

From YourCalendar.csv;

* DI just stands for data island and is renamed to not be linked on to the rest of the model.

then create variables i.e:

vYear =DI_Year

You can drop the DI_* fields as listboxes on the front end.

Then in your charts, use the variable in set analysis.

for example:

sum(Sales) on tab 1 for qvd 1 would become sum({<PeriodForTab1 = {$(vYear)}>}Sales)

sum(Sales) on tab 2 for qvd 2 would become sum({<PeriodForTab2 = {$(vYear)}>}Sales)

Although data islands are not best practise, imo it seems to be the easiest way to get a common date selection across all your different tabs and qvds. Best option would be to seperate the app into smaller ones and only keep data that is related.

[#3]

Hi,

Thanks for your suggestion. Your ideology may work, but the thing is: Period related data come from every QVD I have. So if i extract Year , Month columns from them, I would get Syn. keys, because they have same name apart from the Unique column name. Please suggest how to overcome Syn keys.

- JB

[Anonymous]

Hi,

Many questions can arise from a data model perspective.

How does the data relate to each other?

Can a few of the sources be concatenated to overcome synthetic keys,improve performance,clean up the data model?

If not, can the app be separated into smaller apps with a focus on specific reporting areas?

If you decide to go with the above suggestion - Data Island, You will have to rename each of the common period fields to be specific to that source i.e order_year, sales_year , sales_month etc.

Using DI_Year from your stand alone table will be a common way of having one calendar to select from and will apply it to all other sources using the expression:

sum({<sales_year= {$(vYear)}>}Sales)

or for the orders source

sum({<order_year= {$(vYear)}>}Orders)

Hopefully this helps...

[#5]

What do you mean by stand alone table here ? From which QVD(s) I should load period columns in this table ?

If i load period fields to be specific to that source i.e order_year, sales_year , sales_month etc , how will I relate them to the stand alone table ? If I load stand alone table data into List box and make selections , will it reflect the data in the tabs ?

[Anonymous]

The stand alone table is referred to as a data island which will be renamed as previously posted i.e Year as DI_Year

so that it is not associated with other tables.

the variable will hold the value of Year

vYear =DI_Year

Set analysis will help use the stand alone table selection and apply it to the table(s) of your choice.

sum({<order_year= {$(vYear)}>}Orders)

see attached sample.

Notice the tables are not linked but the data island allows to pass selections using set analysis.

[#7]

Your example and the other example by rwunderlich here->  https://community.qlik.com/thread/66717‌ helped me to create date island. Actually it works well (Thanks to both of you !) . One thing I am worried about is , in one of my table whose date also should be part of date island but it does not have the same key column present . Is it possible to make that table's date also a part in my date island ?

-JB

[Anonymous]

Do you mean it is in the a different format? If not, can you post few lines sample data of what you mean?

[#9]

I have used 'Datelink' concept (from Rob) for data island rather than your 'Unlinked' concept. Now I am trying your concept and i have following doubts.

1.) Your variable 'vDIYearMonth' has this formula:

     =if(GetSelectedCount(DI_YearMonth)=0,MaxString(DI_YearMonth),DI_YearMonth)

     To understand this , I placed a text object with the expression GetSelectedCount(DI_YearMonth) which always show 0 even if any of the dates are selected from DI_Year and DI_Month list boxes. It only shows the selected count if the value from DI_YearMonth list box is selected which is correct as per the variable's formula. I am wondering how QlikView still shows the correct month and year data in the table even if we use DI_Year and DI_Month alone for selected (leaving DI_YearMonth list box untouched).

2.) My requirement is different. If no value is selected in the Date and Year list boxes (i don't have yearMonth list box) then all the  values for all the year and month should be displayed.Please help me on modifying your formula to acheive this .

thanks,

JB

[rwunderlich]

If you are using a Date Link table, then you probably don't need any GetSelected count, etc. Just

sum(Sales)

or if you have multiple data types, possibly:

sum({<DateType={OrderDate}>} Sales)

-Rob