topic Re: Set Analysis Expression For Balance Sheet Accounts in QlikView

Set Analysis Expression For Balance Sheet Accounts

asmithids — Sun, 10 May 2015 21:40:28 GMT

Hello,

I have the following expression for YTD sum. The issue is that I need to sum account transactions from the beginning of time to the current Year-Month selection for a Balance Sheet report. Balance Sheets are an accumulating financial report that maintains a running total from the beginning of time. I'm not sure how to alter the expression below to start the summation from the beginning of time (i.e. 2007) up to and including the Year-Month selection.

Thank you in advance for assistance with this question.

Sum({$<Year={$(=Max(Year))},Month={"<=$(=Max(Month))"}>}TransactionAmount))

Re: Set Analysis Expression For Balance Sheet Accounts

swuehl — Sun, 10 May 2015 21:48:47 GMT

I think it would be easier to create a field containg the Year-Month, i.e. the Monthstart date showing unique values for every combination of Year and Month (I assume Year and Month fields have a numeric representation in your data model):

In your load script, something like this:

LOAD

Year,

Month,

Makedate(Year, Month) as MonthStart

FROM ...

Then:

Sum({$<Year=, Month=, MonthStart = {"<=$(=Max(MonthStart))"}>} TransactionAmount))

Re: Set Analysis Expression For Balance Sheet Accounts

Anonymous — Sun, 10 May 2015 22:19:45 GMT

My 2 cents... actually only 1 cent:

Set analysis is "format-sensitive". In the example from Stefan the MonthStart field is in the date format, but max(MonthStart) will be integer. To make it work, a small change is needed:

Sum({$<Year=, Month=, MonthStart = {"<=$(=date(Max(MonthStart)))"}>} TransactionAmount))

Re: Set Analysis Expression For Balance Sheet Accounts

asmithids — Mon, 11 May 2015 00:50:21 GMT

Thank you swuehl. That did the trick!!!

Re: Set Analysis Expression For Balance Sheet Accounts

asmithids — Mon, 11 May 2015 00:51:20 GMT

Hi Michael,

I made the change. Thank you!