6 Replies Latest reply: May 31, 2017 2:40 AM by Tim Poismans

# Sum based on date in set analysis

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

• ###### Re: Sum based on date in set analysis

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)

• ###### Re: Sum based on date in set analysis

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.

• ###### Re: Sum based on date in set analysis

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.

• ###### Re: Sum based on date in set analysis

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?

• ###### Re: Sum based on date in set analysis

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.
• ###### Re: Sum based on date in set analysis

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.