topic Display Current AND Year End Balances in QlikView

Display Current AND Year End Balances

Wed, 30 Jun 2010 12:39:21 GMT

Hi all,

I have a file showing Balance by Account for each month of the year. I'd like the user to be able to select a month and see a table showing the Year End Balance and the current (ie selected) month balance, and to calculate a Year-to-Date Balance. But I can't figure out how to do this because whenever I select a month I lose all my Year End balances.

I'm guessing I'm going to need to use Set analysis, but I've never really got my head around how to do that.

Thanks for any suggestions,

Tony

Display Current AND Year End Balances

Wed, 30 Jun 2010 12:42:17 GMT

this might help you.....

http://community.qlik.com/forums/t/19567.aspx

Display Current AND Year End Balances

Wed, 30 Jun 2010 15:51:06 GMT

Thanks, that was quite useful, and re-reading the documentation helped too. I have included a 'ReportingMonth' field on load and I calculate the Year End balance using:


Sum({1<ReportingMonth={0}>} Balance)

However, now I'd like to make sure that even when the user hasn't selected a month, the 'Selected_Balance' column displays the most recent balance. At the moment it is displaying the sum of all balances, which is a bit meaningless. I've tried using:


Sum({1<ReportingMonth={Max(ReportingMonth)}>} Balance)

but this doesn't work - I just get '-' in the chart.

Any ideas?

Thanks,

Tony