Qliksense Dynamic Data Filtering

[tvsr111]

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

[tvsr111]

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

[Oliver_F]

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?

[tvsr111]

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

[Oliver_F]

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.

[tvsr111]

Hi

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

[Oliver_F]

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.

[tvsr111]

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

[Oliver_F]

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

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.

[tvsr111]

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 ?

[tvsr111]

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