topic Re: Common 'Period' filter visible for multiple tabs/table in QlikView

Common 'Period' filter visible for multiple tabs/table

Tue, 15 Nov 2016 12:36:05 GMT

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

Re: Common 'Period' filter visible for multiple tabs/table

Anonymous — Tue, 15 Nov 2016 12:53:13 GMT

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.

Re: Common 'Period' filter visible for multiple tabs/table

Wed, 16 Nov 2016 07:01:18 GMT

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

Re: Common 'Period' filter visible for multiple tabs/table

Anonymous — Wed, 16 Nov 2016 07:17:51 GMT

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

Re: Common 'Period' filter visible for multiple tabs/table

Wed, 16 Nov 2016 08:51:21 GMT

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 ?

Re: Common 'Period' filter visible for multiple tabs/table

Anonymous — Wed, 16 Nov 2016 10:10:54 GMT

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.

Re: Common 'Period' filter visible for multiple tabs/table

Fri, 25 Nov 2016 07:09:38 GMT

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

Re: Common 'Period' filter visible for multiple tabs/table

Anonymous — Fri, 25 Nov 2016 07:34:33 GMT

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

Re: Common 'Period' filter visible for multiple tabs/table

Fri, 25 Nov 2016 11:44:06 GMT

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,

Re: Common 'Period' filter visible for multiple tabs/table

rwunderlich — Fri, 25 Nov 2016 23:58:35 GMT

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

Re: Common 'Period' filter visible for multiple tabs/table

Mon, 05 Dec 2016 06:57:02 GMT

Hi,

In your example you have used the following expression to show the 'Sum' of Qty in the 'Order' table.

Sum({<Order_YearMonth = {$(vDIYearMonth)}>}Qty)

So here in your expression you have a variable vDIYearMonth which will give the 'DI_YearMonth' which is selected in the selected and MaxString(DI_YearMonth) if nothing is selected. Can this be changed to display all the YearMonth's data if nothing is selected ? If so how should I do that ?

-JB

Re: Common 'Period' filter visible for multiple tabs/table

Anonymous — Mon, 05 Dec 2016 08:15:10 GMT

Hi James,

You can create a link table. bring the common fields in link table and drop them from individual table.

Please find attached the example for the same.

Hope it helps you.

Re: Common 'Period' filter visible for multiple tabs/table

Anonymous — Mon, 05 Dec 2016 13:19:55 GMT

If you would like to default the period all periods when nothing is selected, then change the definition of the varable vDIYearMonth to:

=if(GetPossibleCount(DI_YearMonth) <> 1, '*',DI_YearMonth)

* changed above

This will give you all YearMonth periods if nothing is selected instead of showing the latest period only.

Re: Common 'Period' filter visible for multiple tabs/table

Wed, 07 Dec 2016 07:10:03 GMT

Hi,

This version has a lag in showing the months if more than one month is selected. See below snapshot.

Here you can see data for months 01 and 02 for 2016 are displayed , but all the data from 2015 are also displayed which is not selected. How can we modify the variable to display the selected months alone ?

-JB

Re: Common 'Period' filter visible for multiple tabs/table

Anonymous — Wed, 07 Dec 2016 08:08:04 GMT

Hi JB,

Please see attached.

It’s fairly flexible...Pay attention to the variables

Although I must agree that the Link table may be a better option. Up to you..

Hope this helps!

Re: Common 'Period' filter visible for multiple tabs/table

Wed, 07 Dec 2016 09:04:47 GMT

Your Concat variable worked for my req.

Thanks,

Re: Common 'Period' filter visible for multiple tabs/table

Thu, 08 Dec 2016 06:54:54 GMT

One last doubt. Can the order and sales tables be combined into one single table ? I mean to display a common YearMonth dimension and display Sum of order and Sum of sales in expression ?

-JB.

Re: Common 'Period' filter visible for multiple tabs/table

Anonymous — Thu, 08 Dec 2016 06:57:39 GMT

Hi JB,

Yes it can be done that way.

Re: Common 'Period' filter visible for multiple tabs/table

Thu, 08 Dec 2016 07:04:59 GMT

Thanks for your reply.

But specifically we are adding the Order_YearMonth or Sales_YearMonth in the dimension , right ? Do we need to add both the dimensions together in one table, align the sorting and hide anyone ? Or does this need to be combined in script itself ? If so how it should be combined ?

-JB

Re: Common 'Period' filter visible for multiple tabs/table

Anonymous — Thu, 08 Dec 2016 07:25:20 GMT

Hi,

See attached.

Basically create one field for YearMonth and a new field to flag Sales or Orders.

Thanks,