6 Replies Latest reply: Dec 1, 2011 10:54 AM by sergiovery RSS

    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.

        • Re: Filtering with variables
          Nagaian Krishnamoorthy

          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

            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
                Nagaian Krishnamoorthy

                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

                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

                  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

                      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.