topic Re: How can i sum all records until a specific date? in QlikView

How can i sum all records until a specific date?

Mon, 02 Jul 2012 08:46:02 GMT

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?

Mikael

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

jjfabian — Mon, 02 Jul 2012 09:06:43 GMT

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

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

Mon, 02 Jul 2012 09:29:30 GMT

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...

Mikael

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

Mon, 02 Jul 2012 09:49:13 GMT

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