Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.


Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Filtering with variables

Hi all,

i've a table with some fields, one of this field is called DTConsCustOrdToUGO and i need to filter it using startDate and EndDate.

I've done these two calendar and i've assigned a trigger event on the variable (only for StarDate) but i need to filter DTConsCustOrdToUGO between StartDate and EndDate.

Anyone can help?

attacched the file i've done.

6 Replies
Not applicable

Re: Filtering with variables

Selecting a range of values in a ListBox based on the beginning value and ending value of the range specified in two other (Slider or Calendar or ListBox) objects is a topic addressed in the forum several times in the past by several QlikView experts.

Based on the knowledge gained from those posts, I have attached a qvw file giving one way of solving this. I do not take the credit for the solution.

Hope this helps.

Not applicable

Re: Filtering with variables

Thank you for your reply.

But i'm newby about qlikview and i really don't understand how you have solved the problem.

I'm using QV8.5 and don't know if this version has a bug but on my installation the selection won't work..

Have you done a trigger? a macro? please tell me how you've solved the problem.

Best regards


Not applicable

Re: Filtering with variables

I use version 10. Here is the list of what I did.

(1) I added a listbox for the field DTConsCustOrdToUGO.

(2) I did not use macros.

(3) I added event triggers for the fields DataFine and DataInizio for OnInput and OnChange events.

(4) The trigger action is:

     Select in Field

     Field:     DTConsCustOrdToUGO

     Search String:     ='>='&Date(DataInizio)&'<='&Date(DataFine)

Not applicable

Re: Filtering with variables

Thank you for your fast reply krishnamoorthy.

Unfortunatly i've understand that your method is not supported on my version.

I've to find another way to solve the problem.

If anyone can help , in the meantime i'll check the forum.

Best regards

Not applicable

Re: Filtering with variables

Hello there!

I'm really a very entry-level user both in SQL and in QV. So...please be patient with me! ;-)

I'm in the same situation: I've to filter the values of Turnover by a date-range set by the end-user.

Here is the situation:

1. I have a pivot-table with 3 columns:

     1.1. Sales manager name

     1.2. CurrentYear Turnover --> sum({<Year={$(=[Year]-1)}>} [Invoiced Sales])

     1.3. PreviousYear Turnover --> sum({<Year={$(=[Year]-1)}>} [Invoiced Sales])

2. I have 2 objects on the canvass created on the base of 1 field during the DBSource importation:

     2.1  SQL=>  Year('Date') as Year --> With this filed I've created the first object: the "Year" table

     2.2  SQL=>   Month('Date') as Month --> With this filed I've created the second object: the "Month" table 

The 2 objects used on the canvass filter the pivot-table on the base of the Year/Month information...but here is the problem of mine.

This engine visualize the value "per month" (as correctly requested...). This because I was not able to realize what I really need!

I would like to filter the pivot-table referring, instead to Year/Month, to specific date-range (i.e. from 15/01/2011 up to 22/04/2011).

I've spend a lot of time in trying to find out the solution with a (welkome) data-range-filter object...but it seams to me that this object doesen't exist :-(

I've also tried to adapt the nice example of Krishnamoorthy....but again I failed :-(

Can anybody help me!?

How do I have to create the StartDate object and the EndDate one on my canvass in order to filter the pivot-table accordingly to the dates set by the end-user?

Thanks for your support

Not applicable

Re: Filtering with variables


Finally I got the result...and it was quite easy!

here the step-by-step creation I've used. If someone has suggestion to improov it...welkome!


Assuming a popolated DB source (in my case it is a 500'000records DB) with these fields:
- CustomerID
- ProductID
- AreaID
- Date
- Turnover

Importation Script:
ODBC CONNECT TO [MS Access Database;DBQ=...\Repository\SourceDB.mdb];
SQL SELECT `CustomerID` as CodCli,
`ProductID` as CodProd,
`AreaID` as CodArea,
Format(`Data`, 'yyyy-mm-dd') as SimpleDate,
`Invoiced Sales` as Turnover,


1. I add 2 variables in Variables Manager
vDataMin - with vDataMin=Min(SimpleDate)
vDataMax - with vDataMax=Max(SimpleDate)
2. I create 2 Slider/Calendar objects on my canvass with these parameters:
Object 1
Style: Calendar
Title: "From date"
Field: Variable --> vDataMin
Minimum value: Min(SimpleDate)
Maximum value: Max(SimpleDate)
Modus: "Single value"
Object 2
Style: Calendar
Title: "Up to date"
Field: Variable --> vDataMax
Minimum value : vDataMin
Maximum value: Max(SimpleDate)
Modus: "Single value"
3. In a new Table Object I create 2 expressions to calculate the turnover sum by Customer/Product/Area
Dimensions: CodCli/CodProd/CodArea

Expression Titles: "TurnoverCY"; "Turnover PY" (Current yera/Previous year)
for "TurnoverCY" --> Sum({< SimpleDate = {'>=$(=Date(vDataMin))<=$(=Date(vDataMax))'} >} Turnover)
for "TurnoverPY" --> Sum({< SimpleDate = {'>=$(=Date(addmonths(cDataMin,-12)))<=$(=Date(addmonths(vDataMax,-12)))'} >} Turnover)
...and that's it!

Note - Being using QV with the Local language, it maybe some titles/elments have different titles in your english/local language.