Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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