6 Replies Latest reply: Jun 10, 2012 6:54 AM by ervin jafari RSS

    Between function

      Is there any way to get a between function in my expression?  I want to get the paid amount for items that are 1-3 days late.  I can use the below expression but the next expression is for 4-14 days late and that would get lengthy really quickly.

       

      Thanks,

      Kristy

      Sum(if([Due Date]+1=TranDate and [Summary Date]=[Due Date] and SummaryLoanNbr=PaidLoanNbr, PaidAmt,))+

      Sum(if([Due Date]+2=TranDate and [Summary Date]=[Due Date] and SummaryLoanNbr=PaidLoanNbr, PaidAmt,))+

      Sum(if([Due Date]+3=TranDate and [Summary Date]=[Due Date] and SummaryLoanNbr=PaidLoanNbr, PaidAmt,))

        • Re: Between function
          Angus Monro

          Hi Kristy,

           

           

          A few thoughts. 

           

          1. The shortest answer is to reduce your sample to:

           

          Sum(if(1<=TranDate and TranDate <=[Due Date]3 and [Summary

          Date]=[Due Date] and SummaryLoanNbr=PaidLoanNbr, PaidAmt))

           

          2. I'm not sure if this is possible in your data model without seeing it

          more fully, but if possible, classify your due dates on script-side

          using an INTERVALMATCH LOAD.

           

          3. if your actual problem is something like making a bar chart, with a

          bar per Due Date bucket, then you'll want to use an aggr() function with

          the interval logic in it as part of the expression for a calculated

          dimension; then your plotted expression doesn't need the condition at

          all.

           

          Angus.

            • Between function

              I am actually using it in a pivot table.  I am not very familiar with Qlikview - so, i am not very familiar with the AGGR function.  I am attaching the file and would appreciate your feedback in order to make option 3 work. 

               

              Thank you,

              Kristy

                • Between function
                  Angus Monro

                  Hi Kristy,

                   

                  your file didn't successfully attach, I'm afraid.  What I'd like to do, though, is explain aggr() and give you a method for how to convert your requirement into the appropriate expressions.  This will hopefully help you, then, with other problems.  It does mean I'm going to answer in some length, though, so stay with me!

                   

                  The aggr() function lets you calculate new fields for your data model on-the-fly.  This is especially useful when you are designing a chart, and the dimension you want for your chart isn’t one of the fields already in your data model. In fact, you can think of aggr() as creating a new table of data in your data model, containing the already-existing fields plus one new field that is calculated for each combination of the already-existing fields. 

                   

                  Let’s illustrate with an example.  Suppose I am a chair salesman (very stylish, Scandinavian chairs, of course).  I have gathered sales data in a table that has 3 fields: the Customer, the SaleDate, and the NumberOfChairs sold to the customer on that date.  I want to make a bar chart showing how many customers have bought 1-5 chairs, 5-20, or 21+.  How to do this?  Clearly, the dimension on my chart is a sales range, but my data doesn’t explicitly contain the range that my customers fall into.

                   

                  You can see, can’t you, that this challenge would probably be easier if my original data had a summary table that showed Customer and total number of chairs sold.  In fact, it really would be easy if the summary table simply had the sales range for each customer (where sales range is one of the values “1-5”, “5-20” or “21+”).  This where aggr() comes in.  If you can complete this sentence: “this problem would be easy if my data model had the X for each combination of values of A, B, C, …”; then you simply want aggr(X, A, B, C, …) as your dimension expression.  QlikView will then calculate X for each A, B, C, …; QlikView will dynamically associate it with the rest of your data; and you can then make your chart expression to be whatever it needs to be for each value of X

                   

                  Well, then, apparently I therefore want (in pseudocode) aggr(sales range, Customer).  But how do we calculate sales range?  Well, after a touch more thought, we realise this is actually (still in pseudocode) aggr(IF total number of chairs sold<=5 THEN ‘1-5’ ELSE IF total number of chairs sold<=20 THEN ‘6-20’ ELSE ‘21+’, Customer).  Calculating the total number of chairs sold is easy, and this gives us our final expression:

                  aggr(

                  IF(       SUM(NumberOfChairs)<=5 , ‘1-5’,

                  IF(       SUM(NumberOfChairs)<=20, ‘6-20’,

                                                     ‘21+’

                  )),

                  Customer

                  )

                  Ta Da!  We add this to our bar chart as a calculated dimension.  Then, because we want the bars themselves to be the number of customers, we specify our chart expression to be COUNT(Customer)

                   

                  Let’s now take a step back and reverse-engineer what QlikView is doing here when it generates our bar chart. 

                  1. First, it examines the Dimensions for calculated expressions and finds our aggr()-based expression. 
                  2. QlikView then proceeds to evaluate our IF… expression for every value of Customer.  In other words, it goes to our original data table, and then for each Customer it sums NumberOfChairs and uses our logic to decide if this Customer’s respective aggregated value is ‘1-5’, ‘6-20’ or ‘21+’.  This results in a new table with Customer as one column and our calculated sales range for each customer as the other. 
                  3. QlikView then automatically links this table into our existing data model i.e. to our original table with its columns of Customer, SalesDate and NumberOfChairs.  Hence, the data model now has two tables: the original, and the new one generated by the aggr(). 
                  4. QlikView now uses the first parameter of the aggr() – which is our calculated sales range field - as the dimension for the chart.
                  5. QlikView next proceeds to evaluate the chart’s expression, COUNT(Customer).  This is actually very simple for QlikView in this example, because the table resulting from aggr() has Customer as one of its columns anyway! 

                  So, now QlikView has calculated everything it needs to generate the chart we desire.

                   

                  So, remember the key to solving the problem: complete the sentence “this problem would be easy if my data model had the X for each A, B, C, …”.


                  Let me know how you go!

                   

                  Angus.