How can i sum all records until a specific date?

[#1]

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

[jjfabian · Accepted solution]

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 😞

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

Regards,

Jakob

[#3]

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

[#4]

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