topic Re: Opening Balance and Date Change Issues in QlikView

Opening Balance and Date Change Issues

Wed, 25 Nov 2015 10:15:21 GMT

Hi,

I am trying to change an expression for an opening balance figure.

It currently works by summing up all previous periods I have which I have tied to a field called PeriodIndex, which is just numbers from 1-180.

I am now trying to change that to work by a calendar format, i.e Periods 1-12 and Years. I have managed to tie these to PeriodIndex so they do change accordingly, i.e 2016, Period 6 will relate to the correct PeriodIndex number out 1-180.

However I am unable to get my opening balance figures correctly.

My original code to sum up all previous periods and to use the minimum selected Periodindex number in a selection was:

Sum({<PeriodIndex={"<$(=Min(PeriodIndex))"},..........Amount)

In an ideal world I could surely transform this code already to select the minimum Fiscal Year and Period to make it something like this i.e:

Sum({<FY={"<$(=Min(FY))"},{<Period={"<$(=Min(Period))"},..........Amount)

However this expression or versions I've been trying always starts the years first month at 0.

To perhaps somewhat complicate the matter the opening balance figure is then used to create a closing balance figure for the Period and then in turn this closing balance figure becomes the opening of the next period.

Any help on the matter would be greatly appreciated.

James

Re: Opening Balance and Date Change Issues

marcus_sommer — Wed, 25 Nov 2015 11:43:02 GMT

I thinkk if your calendar is further connected to the PeriodIndex you didn't need to change your approach - you couldn't make it easier.

- Marcus

Re: Opening Balance and Date Change Issues

Wed, 25 Nov 2015 11:51:17 GMT

My calendar and periodindex are connnected howvever my calendar is based on FY and Periods and PeriodIndex is just a list of numbers.

So when you select a different year my number for opening balance reverts to 0.

Is there any way I can get it to incorporate a previous year figure?

Re: Opening Balance and Date Change Issues

marcus_sommer — Wed, 25 Nov 2015 13:10:23 GMT

It there is the right connection a PeriodIndex should work - it starts by 1 parallel to your calendar (financial calendar meant only a certain offset) and adds each month with 1. I use sometimes a similar aproach with a MonthCounter: Re: Howto make a variable to calculate quarters regarding year changes? And this is maybe also helpful for you:

How to use - Master-Calendar and Date-Values‌.

- Marcus