Set Analysis for everything below a selected date

[#1]

Hello all,

Does anyone know how to use Set Analysis to select all dates in a certain field which are below or equal to the one that is selected by the user?

My situation is this, I want to calculate the sum of payments for every customer but I want to let users be able to see what was the situation on a certain date. For example: We make an invoice of 600 on 1/1/2012, the customer pays 250 on 13/1/2012 and he pays 350 on 16/1/2012. Making the sum as of today 0 (-600+250+350). If the user wants to know what was the situation on 14/1/2012, we only need to calculate all the lines which have a date lower or equal to 14/1/2012. Result he needs to see is (-600+250) -350.

We have a line for every detail with a common link (EntryNo).

How can I make it like this that when the user selects a date in the list box with dates, he sums everything below or equal to that day?

Thanks in advance for your response.

Niels

[SunilChauhan · Accepted solution]

use

sum({<Date={"<=$(=max(date))"} Value)

hope this helps

[jagan]

Hi,

Try the below expression, you need to exclude the selection

=Sum({<Date={"<=$(=max(Date))"}>} Value)

If there are any date related Listboxes for Year/Month/Quarter/Week/Day

=Sum({<Date={"<=$(=max(Date))"}, YearFieldName=, MonthFieldName=, QuarterFieldName=, WeekFieldName=, DayFieldName=>} Value)

Hope this helps you.

Regards,

Jagan.