topic Re: calculating opening bal in QlikView

calculating opening bal

Wed, 25 Mar 2015 13:27:56 GMT

I want to capture previous value of my closing account as opening account for the next month . Find attach excel. Gives u clue.

Note opening balance should start wt zero.

Re: calculating opening bal

MK_QSL — Wed, 25 Mar 2015 14:34:02 GMT

Can you provide little more information on how your calculated Closing balance?

Re: calculating opening bal

Jacek — Wed, 25 Mar 2015 14:59:18 GMT

in expression Openning column : Above([sum(CLOSING_BALANCE)],1)

Above ( [name_source_column] , offset )

Re: calculating opening bal

chematos — Wed, 25 Mar 2015 15:00:12 GMT

You should take a look to this:

Calendar with flags making set analysis so very simple

If you don't use the "AsOfCalendar", then try with the following solution.

Lets say you have a numeric DateID for each Date field in your calendar, you could use it in your expression.

Your expression for Opening Balance should be something like that, in case your closing balance is a data:

Sum({<DateID={'$(=DateID-1)'}>} Closing Balance)

If not, use this set analysis to replicate the calculations for current Closing Balance, in order to calculate the previous closing balance.

Regards,

Jose

Re: calculating opening bal

Wed, 25 Mar 2015 15:18:40 GMT

Hello Jose,

Find attached what i got not working

Re: calculating opening bal

chematos — Wed, 25 Mar 2015 15:29:46 GMT

change Date(Date) to Rowno() as DateID, make sure you have the dates ordered by ascendent.

Then, you can use for your chart Above() function to calculate Opening Balance:

Above([Closing balance])

Regards,

Jose

Re: calculating opening bal

Wed, 25 Mar 2015 15:32:59 GMT

This is what i got

Re: calculating opening bal

chematos — Wed, 25 Mar 2015 15:38:51 GMT

Check this

Re: calculating opening bal

Wed, 25 Mar 2015 18:13:55 GMT

Its not picking above for Jan 2015, that is an issue

Re: calculating opening bal

Wed, 25 Mar 2015 18:29:02 GMT

Hello Manish,

I want the Closing balance of the previous month to be the opening of the next as it is on the excel starting with zero

Re: calculating opening bal

Jacek — Wed, 25 Mar 2015 18:35:35 GMT

example in attachment

Regards

Jacek

Re: calculating opening bal

chematos — Wed, 25 Mar 2015 18:37:22 GMT

Hi,

Try to use just 1 dimension in your chart, creating MonthYear field in the loading script:

Load*,

Month&'-'&Year as MonthYear;

load*,

Month(DateID) as Month,

Year(DateID) as Year

Regards,

Jose

Re: calculating opening bal

Wed, 25 Mar 2015 20:35:18 GMT

When in drill down Dec 2014 doesnt post closing balance to opening balance

Re: calculating opening bal

Wed, 25 Mar 2015 22:26:51 GMT

I have to split into month and year to achieve what i want