3 Replies Latest reply: Jul 2, 2012 5:49 AM by Apurva Jha

# How can i sum all records until a specific date?

Hi,

I need to sum all records (Amount) for all transactions up until a selected date period, i.e. a user selects a date  and wants the aggregated sales for all periods up to (but not including) the selected one. Plus i will need the same function for the selected period and all records after.

Any suggestions?

Br

Mikael

• ###### Re: How can i sum all records until a specific date?

Hej Mikael,

use set analysis here. You should add something along these lines (set analysis in italics - you may have to adapt it to fit your time dimension[s] ):

sum({\$<Date= {"<=\$(=max(Date))"}>} Amount)

Regards,

Jakob

• ###### Re: How can i sum all records until a specific date?

Thank you!

i figured this out;

sum({\$ < DocumentYear =, DocumentMonth =, DocumentYearMonth=, DocumentQuarter = >} if(DocumentYearMonthNumeric < \$(#=max(DocumentYearMonthNumeric)), Amount))

Your answer gives the same output and I assume set-analysis is a bit faster than using if...

Br

Mikael

• ###### Re: How can i sum all records until a specific date?

Hi Mikael,

there are two methods to show the sum.

1.  =sum( {< DATE={"< \$(=max(DATE))"} >} AMOUNT) by Default this will show the sum of amount excluding the maximum date's amount. And after selection of a date it will give u the output as u want.

2.  =sum( {< DATE={"< \$(=(DATE))"} >} AMOUNT)  by Default it will show 0 untill u select a date and after selection it will give u correct output.

Thanks & Regards

Apurva