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

[balabhaskarqlik]

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.

[Oliver_F]

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.

[tvsr111]

Hi,

We implemented this for the Metrics and its working.

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

please suggest

[Oliver_F]

can you provide some example values of your dimension?

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

[tvsr111]

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

[Oliver_F]

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.

[tvsr111]

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

[Oliver_F]

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)