Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all
In request of a customer, I'm trying to make a table that lists an amount paid over the course of a few date ranges.
The customer wants to see how much was paid after 1 month, 2 months, 3 months, 6 months,...
I decided to focus on the 1 month. With this working, the rest should be a walk through the park.
Consider the following example data:
Filenr | Date creation | date action | End of range date | Check if action in range | text1 | Amount |
GM57566 | 10/01/2011 | 14/01/2011 | 10/02/2011 | T | payment | 202,75 |
GM57568 | 10/01/2011 | 4/02/2011 | 10/02/2011 | T | payment | 30,28 |
GM57570 | 10/01/2011 | 15/02/2011 | 10/02/2011 | F | payment | 888,71 |
GM57572 | 10/01/2011 | 25/01/2011 | 10/02/2011 | T | payment | 563,62 |
GM57573 | 10/01/2011 | 18/01/2011 | 10/02/2011 | T | payment | 55,51 |
GM57573 | 10/01/2011 | 18/01/2011 | 10/02/2011 | T | payment | 235,47 |
GM57575 | 10/01/2011 | 27/01/2011 | 10/02/2011 | T | payment | 455,93 |
GM57578 | 10/01/2011 | 24/03/2011 | 10/02/2011 | F | payment | 122,70 |
GM57579 | 10/01/2011 | 14/04/2011 | 10/02/2011 | F | payment | 550,54 |
GM57580 | 10/01/2011 | 5/05/2011 | 10/02/2011 | F | payment | 100,05 |
GM57580 | 10/01/2011 | 3/03/2011 | 10/02/2011 | F | payment | 182,00 |
GM57580 | 10/01/2011 | 3/02/2011 | 10/02/2011 | T | payment | 182,00 |
GM57580 | 10/01/2011 | 5/04/2011 | 10/02/2011 | F | payment | 91,00 |
GM57580 | 10/01/2011 | 16/06/2011 | 10/02/2011 | F | payment | 22,79 |
GM57585 | 10/01/2011 | 27/01/2011 | 10/02/2011 | T | payment | 99,47 |
GM57586 | 10/01/2011 | 24/01/2011 | 10/02/2011 | T | payment | 576,61 |
GM57586 | 10/01/2011 | 30/03/2011 | 10/02/2011 | F | payment | 47,17 |
GM57586 | 10/01/2011 | 3/02/2011 | 10/02/2011 | T | payment | 576,62 |
In the data, we have the Date creation: the date the file was created and the start date of the range.
End of range date: the end date of the range. This is simply Date creation + 1 month.
date action: the field that needs to fall in the range.
The field 'Check if action in range' gives a T(rue) or F(alse) if the field is in range.
Now, lets take Filenr GM57586 for example
The filenr occurs 3 times, of which two fall in the range. Now I want to make a list, for each Filenr, which list the total Amount that falls within that range. For Filenr. GM57586 this would be 576.61 + 576.62 = 1153.23
I tried to achieve this with the following formula, which doesn't work:
Sum({<text1={'payment'}, [date action]={"<=$(=date(AddMonths([Date creation]),1)))"}>}Amount)
Example of the desired table:
Filenr | Amount 1M |
GM57566 | 202,75 |
GM57568 | 30,28 |
GM57570 | |
GM57572 | 563,62 |
GM57573 | 290,98 |
GM57575 | 455,93 |
GM57578 | |
GM57579 | |
GM57580 | 182,00 |
GM57585 | 99,47 |
GM57586 | 1153,23 |
Thanks in advance and kind regards,
Tim
When nothing is selected, then more than one [Date creation] value is possible, which means that Num(AddMonths([Date creation],1)) is null. You need an aggregation function (like min() or max()). For example:
Sum({<text1={'payment'}, date={"<=$(=Num(AddMonths(Max([Date creation]),1)))"}>}Amount)
This will still track your selections, but will also work when nothing is selected.
However, if you need a row by row comparison of [date] and [Date creation], then the set expression will not work. The set expression is evaluated once for the chart, not row by row.. The options are then
Your expression looks correct except for an misplaced closing parenthesis - change to:
Sum({<text1={'payment'}, [date action]={"<=$(=date(AddMonths([Date creation]),1))"}>}Amount)
Sum({<text1={'payment'}, [date action]={"<=$(=date(AddMonths([Date creation],1)))"}>}Amount)
^^^^^
(edited)
The inner date() function may be redundant:
Sum({<text1={'payment'}, [date action]={"<=$(=AddMonths([Date creation],1))"}>}Amount)
These expressions assume that all the date fields are proper QV numeric date values, not strings.
Yes, noticed the parenthesis and checked, was an error with copying and scrambling the data.
The fields are QV numeric date values, first thing I checked.
But the only thing I can imagine is that there's something wrong with the formatting of the date fields.
Going to double check it again.
Ok, update on the issue:
I got it working, although not completely.
Fiddled with the formula a bit, eventually ending up to just using Num instead of Date.
Sum({<text1={'payment'}, date={"<=$(=Num(AddMonths([Date creation],1)))"}>}Amount)
The only issue I have at the moment, is that it works when I select one value. When I want to show a list for all my files, the formula returns 0.00 across the board.
Any advice on the matter?
When nothing is selected, then more than one [Date creation] value is possible, which means that Num(AddMonths([Date creation],1)) is null. You need an aggregation function (like min() or max()). For example:
Sum({<text1={'payment'}, date={"<=$(=Num(AddMonths(Max([Date creation]),1)))"}>}Amount)
This will still track your selections, but will also work when nothing is selected.
However, if you need a row by row comparison of [date] and [Date creation], then the set expression will not work. The set expression is evaluated once for the chart, not row by row.. The options are then
Hey Jonathan,
Even though there's only one [Date creation] per file, the formula is calculated on chart level, so it won't work when nothing is selected, okay.
There's more than a million records, a Sum(If()) doesn't seem like it's the best solution.
Had hoped I wouldn't have to dive in the script as it is not my own. Though seems like that would be the best solution.
Thank you, Jonathan.