Sum based on date in set analysis

[timpoismans]

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

[jonathandienst]

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)

[jonathandienst]

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.

[timpoismans]

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.

[timpoismans]

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?

[jonathandienst · Accepted solution]

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

• Sum(If()) - but this can be slow if the data set is large.
• Create a derived flag field in the load script where 1 means "date in window". Then you can use a set expression with the flag field in the front end.
  

[timpoismans]

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.