topic Re: Filtering with variables in QlikView

Filtering with variables

Sat, 18 Jun 2011 20:46:06 GMT

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.

Re: Filtering with variables

nagaiank — Sat, 18 Jun 2011 22:29:38 GMT

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.

Re: Filtering with variables

Sun, 19 Jun 2011 19:01:26 GMT

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

Andrea

Re: Filtering with variables

nagaiank — Sun, 19 Jun 2011 21:11:14 GMT

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)

Re: Filtering with variables

Mon, 20 Jun 2011 10:01:25 GMT

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

Re: Filtering with variables

Mon, 28 Nov 2011 12:07:07 GMT

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

Re: Filtering with variables

Thu, 01 Dec 2011 15:49:28 GMT

Eureka!

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!

Sergiovery

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,
`Data`,
Format(`Data`, 'yyyy-mm-dd') as SimpleDate,
`Invoiced Sales` as Turnover,

FROM MainDB;

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)
Expressions:
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.