topic Re: Qliksense Dynamic Data Filtering in App Development

Qliksense Dynamic Data Filtering

tvsr111 — Mon, 27 Apr 2020 05:47:54 GMT

Hi,

we have a Star schema model where the Fact table is SCD type 2 which holds balances by the dates.

Fact table has 2 columns Valid_From_Date and Valid_To_Date which should be used to select data for a specific date

When User opens an app, he has to select a date first and based on that selected date, data should be populated.

please suggest how to implement this requirement

Re: Qliksense Dynamic Data Filtering

BalaBhaskar_Qlik — Mon, 27 Apr 2020 11:56:42 GMT

Create a master calendar, Try with calendar extension, to select the date ranges, add an set analysis expression to include the date selections in it.

There are other extensions too available to pass/select a Default value, on opening a sheet & filtering the data based on it.

Re: Qliksense Dynamic Data Filtering

Oliver_F — Mon, 27 Apr 2020 11:59:54 GMT

Hi,

have you tried the following:

1) Create a flying table (a table that is not attached to the datamodel), that contains all the dates, so the user can select a date without filtering the data model.

2) store the selected date into a variable: vVar = only(selectionDate)

3) use vVar in a set analysis to calculate your balance

sum({<From_Date = {"<=$(vVar)"},To_Date = {">=$(vVar)"}>} Value)

You might have to play a little bit with the syntax to get it right. But in Theory this should work.

Re: Qliksense Dynamic Data Filtering

tvsr111 — Mon, 27 Apr 2020 14:34:22 GMT

Hi,

We implemented this for the Metrics and its working.

But we are struggling for non-numeric Character (dimension) attributes

please suggest

Re: Qliksense Dynamic Data Filtering

Oliver_F — Mon, 27 Apr 2020 14:42:11 GMT

can you provide some example values of your dimension?

as long as you can sort those values it should work in the same way.

Re: Qliksense Dynamic Data Filtering

tvsr111 — Mon, 27 Apr 2020 18:46:33 GMT

Main table have Revenue Balances as at position

Fact_Key, Portfolio, ContactName, RevenueToDate, Valid_from_date, Valid_to_date
1, Pepsi, David, 500, 01/01/2017, 12/31/2017
2, Pepsi, Mike, 3000, 01/01/2018, 12/31/2018
3, Pepsi, James, 5000, 01/01/2019, 12/31/2019
4, Coke, Roman, 7500, 01/01/2020, 12/31/2020

Craeted a Flying table with Date values

01/01/2017
01/01/2017
01/01/2017
01/01/2017
01/01/2017

Created a Variable for the date selected on Flying table with default value as Today()

varRepDate=date( Coalesce(GetFieldSelections(DateValue), Today()) , 'M/D/YYYY')

now on QLIK app I want to display Main table data only where varRepDate falls between Valid_from_date and Valid_to_date

varRepDate>=Valid_from_Date AND varRepDate<=Valid_to_Date

please advise how to limit the data being displayed based on Flying table selection or a variable value

Re: Qliksense Dynamic Data Filtering

Oliver_F — Tue, 28 Apr 2020 09:00:13 GMT

hi,

I would recommend to convert the three date fields in your datamodel into a date within the load script. By this you will have numeric values where > and < operators will work.

In the example that you gave us, the from-to-period is always a full year. If this is the case for all your data, i would change the model to have one year-field instead of using from-to.

Re: Qliksense Dynamic Data Filtering

tvsr111 — Tue, 28 Apr 2020 10:03:20 GMT

Hi,

The dates used were only for sample and they not cover a full year always.

Also, not sure if you understand the problem we have. Let me explain below

we need to limit data in the table/visualization based on the date selected on Flying table

created a variable: varDateFilter= GetFieldSelections(DateValue)

now we need to apply a filter to the table/visualization as :

(varRepDate>=Valid_from_date and varRepDate<=Valid_to_date)

Question: where would we have to apply this filter so the data on table/visualization gets limited

Re: Qliksense Dynamic Data Filtering

tvsr111 — Tue, 28 Apr 2020 10:05:22 GMT

Re: Qliksense Dynamic Data Filtering

Oliver_F — Tue, 28 Apr 2020 11:52:36 GMT

HI,

please have a closer look to my first reply on this topic. - Here is the long version of it.

SCRIPT

===========

Input_temp:
load * inline [
Fact_Key, Portfolio, ContactName, RevenueToDate, Valid_from_date, Valid_to_date
1, Pepsi, David, 500, 01/01/2017, 12/31/2017
2, Pepsi, Mike, 3000, 01/01/2018, 12/31/2018
3, Pepsi, James, 5000, 01/01/2019, 12/31/2019
4, Coke, Roman, 7500, 01/01/2020, 12/31/2020
];

NoConcatenate
Facts:
load
Fact_Key,
Portfolio,
ContactName,
RevenueToDate,
date(date#(Valid_from_date,'MM/DD/YYYY')) as Valid_from_date,
date(date#(Valid_to_date,'MM/DD/YYY')) as Valid_to_date
Resident Input_temp;

drop table Input_temp;

FlyingDate:
load
MakeDate(2017 + RowNo()) as Date_zzz
AutoGenerate 4;

======

VARIABLE:

varRepDate =only(Date_zzz)

=====

TABLE

as shown in your Screenshot; Measure = sum({<Valid_from_date = {"<=$(varRepDate)"},Valid_to_date = {">=$(varRepDate)"}>} RevenueToDate)

Re: Qliksense Dynamic Data Filtering

tvsr111 — Tue, 28 Apr 2020 12:10:57 GMT

Hi,

I understand what you are saying.... all that you are focusing is on the Measure attribute only, but the problem I have or trying to ask is much bigger than that

I already achieved this for the Measure attributes

I was mainly asking for the Dimension attributes conditional filtering

For better understanding, ignore the Financial values completely.

Assume I only have below data with out revenue

Fact_Key, Portfolio, ContactName, Valid_from_date, Valid_to_date
1, Pepsi, David, 01/01/2017, 12/31/2017
2, Pepsi, Mike, 01/01/2018, 12/31/2018
3, Pepsi, James, 01/01/2019, 12/31/2019
4, Coke, Roman, 01/01/2020, 12/31/2020

Example1: when I select '01/01/2018' on FlyingDate, I should see only the respective qualifies record in the output as below

Fact_Key, Portfolio, ContactName, Valid_from_date, Valid_to_date
2, Pepsi, Mike, 01/01/2018, 12/31/2018

Example2: when I select '01/01/2020' on FlyingDate, I should see only the respective qualifies record in the output as below

Fact_Key, Portfolio, ContactName, Valid_from_date, Valid_to_date
4, Coke, Roman, 01/01/2020, 12/31/2020

Example2: when I select '01/01/1800' on FlyingDate, then I should not see any data as there is no qualifying data

Hope you now understand the requirement.

We have to apply dynamic filters on full data not just measures but entire data set

Re: Qliksense Dynamic Data Filtering

Oliver_F — Tue, 28 Apr 2020 12:24:12 GMT

Hi,

in that case I do have two more ideas

1) Would it be possible to create a bridge table that connects all Fact_keys to all matching Dates? You should be able to do this quite easily using the interval match function.

2) Would it be possible to use a (paid) extension that sets a filter on the Valid_from and Valid_to field whenever a Date is selected?

Re: Qliksense Dynamic Data Filtering

tvsr111 — Tue, 28 Apr 2020 12:33:25 GMT

Hi,

The example I shown in just a simpler sample version to demonstrate our requirement.

In reality, our fact table have SCD2 history (with From & To Dates) links to multiple Dimension tables. Fact table has about 30 million (30,000,000) rows.

option-1) Bridge table is not a feasible solution with our volumes

Fact has 30,000,000 rows. FlyingDate table will have all the dates since 1900 , so it has about 50,000+ record. By creating bridge table, all the data will be cartesian product and so the overall data on QVF model will be too huge , we will end up with performance issues

We already considered this option and ruled out

option-2) Didnt get what you mean by "a (paid) extension"

can you explain bit more or give an example pls

Re: Qliksense Dynamic Data Filtering

Oliver_F — Tue, 28 Apr 2020 12:46:44 GMT

If you are open to use an extension there might be a very easy solution.

For example our extension "MENUBAR" could solve your issue quite easily.

1) You select a date in your flying table.

2) MENUBAR is triggered by that selection and applies a filter on the fields From_Date and To_Date.

So basically you filter From_Date to < ReferenceDate and To_Date > ReferenceDate, but without doing this manually. Both filters are applied automatically whenever you select a (new) ReferenceDate.

If that is an option for you I am happy to provide more details about our extensions. Otherwise you might find others extensions in the qlik branch too, that might help you with this special request.

Another idea that might work, but I have never used it by myself: You can filter for variables just like you can filter for values. You can even create bookmarks with this. You can set those filters using a variable and store it as a bookmark that is automatically applied when you open the sheet. But i am not sure, if the filters update, when the variable updates.

Re: Qliksense Dynamic Data Filtering

tvsr111 — Tue, 28 Apr 2020 15:55:00 GMT

I managed to acheive this functionality by using the variables

created variable: varRepDate=varRepDate=date( Coalesce(GetFieldSelections(DateValue), Today()) , 'M/D/YYYY')

Then on the visualization/tables, created Dimensions as below

Name= if(varRepDate>=Valid_from_Date AND varRepDate<=Valid_to_Date, Name, '')

Applied same rule for all the attributes in the table/visualisation

with this we were able to see only qualifying records based on FlyingTable selection..... additionaly we endup with an extra blank row for every time.

Is there any way we can hide or ignore the blank row or a row with all attributes empty/blank

please attachment below

Re: Qliksense Dynamic Data Filtering

Oliver_F — Wed, 29 Apr 2020 06:54:01 GMT

Hi,

have you tried: using null() or 0 instead of '' in your if-statement?

null(): you should be able to subpress null-values in the dimension setting

0: you *might* be able to subpress those in the addons setting (but this might only apply to measures)

Regarding my first approach: Instead of adding the if-statement to each column, you could add 1 helper column. just use the statet set-analisys but instead of sum({...}Revenue) use count({...}ID).

If you do not want to see that helper column in your table, you can try hiding it using the show condition. but this might not just hide it but, although disable the effect. you will have to try this.

Re: Qliksense Dynamic Data Filtering

tvsr111 — Wed, 29 Apr 2020 10:42:41 GMT

Hi,

thanks for the response. couple of clarification on both suggestions

1) null(): you should be able to suppress null-values in the dimension setting

where can we find this option to suppress Null values so that respective unqualified rows will be filtered

2) If we use a helper column as count({...}ID)

then how do we limit records so that respective unqualified rows will be filtered

Re: Qliksense Dynamic Data Filtering

Oliver_F — Wed, 29 Apr 2020 10:50:19 GMT

1) https://community.qlik.com/t5/New-to-Qlik-Sense/Qlik-sense-line-chart-hide-dimension-null-values/td-p/18100?attachment-id=11147

2) Set Analysis Magic should do that, because SA basically adds the filters to that measure. Als long as you have no other measures that should do what you want to. If not - the result should be 0 and you can subpress 0-Values in the addons section.

Re: Qliksense Dynamic Data Filtering

tvsr111 — Wed, 29 Apr 2020 13:10:52 GMT

2) Aahhh, I see what you suggested.

even before you mentioned, I tried this option and it does not work for my requirement.

Reason 1: we will have some measures as well along with the helper column...

secondly, by hiding the helper column, the effect also gets diabled

only then I moved to apply conditional check on each attribute.... rather we thinking to create each Dimension/Measure as Master items so they can be reused.... and actual Feild items should never be used.

Can you suggest any other approach to minimise all this over complication ?

Re: Qliksense Dynamic Data Filtering

tvsr111 — Wed, 29 Apr 2020 13:13:21 GMT

Hi,

1)https://community.qlik.com/t5/New-to-Qlik-Sense/Qlik-sense-line-chart-hide-dimension-null-values/td-...

this tells about the option for charts

similar "Include null values" option do not exist for a Table visualization

Let me know if I'm missing something

Thanks for all your help