Discussion board where members can get started with QlikView.
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,))
Solved! Go to Solution.
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.
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.
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.
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
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.
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.
Good afternoon Angus,
I am probably making this a bit more difficult than i should, but this is the formula that i have been trying to input into the dimension for my sheet. I am still having issues with this.
Thanks Nicely explained.