Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Opening Balance and Date Change Issues

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

3 Replies

Re: Opening Balance and Date Change Issues

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

Not applicable

Re: Opening Balance and Date Change Issues

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

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

Community Browser