topic Re: balances in QlikView

balances

Wed, 25 Mar 2015 23:13:13 GMT

I want to capture previous value of my closing account as opening account for the next month . scenario

Year	Month	Opening balance	Closing Balance
2011	Jan	0	237
2011	Sep	237	445
2012	Feb	445	679
2012	Nov	679
			x
Day n		x

Also date; i have about 16 months in which all other 4 months are merged into december. Find attached QVW ffile

Re: balances

petter — Wed, 25 Mar 2015 23:36:10 GMT

You can take advantage of the Above() function like this:

Re: balances

maxgro — Wed, 25 Mar 2015 23:45:46 GMT

bal2.qvw: failed to open

to set opening balance

source:

LOAD Year,

Month,

date(Date#(Year & '-' & Month & '-01', 'YYYY-MMM-DD')) as Date,

[Closing Balance]

FROM

[http://community.qlik.com/thread/157634]

(html, codepage is 1252, embedded labels, table is @1)

where len(trim(Month))>0;

final:

load

alt(peek([Closing Balance]),0) as [Opening balance]

Resident source

order by Date;

DROP Table source;

Re: balances

Thu, 26 Mar 2015 11:04:19 GMT

The value for closing balannce for the last month Dec 2013 isnt moved to the next year january. this is the major issue i have

Re: balances

Thu, 26 Mar 2015 11:23:40 GMT

Hello Massimo,

This cool, but not really what i want. Find attached I have drill down in it and i just want the value of Dec closing bal. to be passed to Jan opening bal. on drill down

Re: balances

petter — Thu, 26 Mar 2015 11:44:36 GMT

If you make sure to have a dimension which is a combination of year and month you will be able to span past years...

Re: balances

Jacek — Thu, 26 Mar 2015 14:40:05 GMT

We cannot open Your QVW file (bal2).

In set analyses You will not do it. You should in LOAD SCRIPT make right calculations and insert previous values in base date row.

Re: balances

petter — Thu, 26 Mar 2015 14:49:48 GMT

Actually the application with test data that I uploaded proves that there is no problem getting closing balance from the previous year. Have a look at that application. If you use a Pivot Table instead of a Straight Table you might have a problem with the Above() function IF your dimension grouping is not correct...

Re: balances

Fri, 27 Mar 2015 09:24:49 GMT

Am having issues with uploading please download this link:

Dropbox - balance1.qvw

Thanks

Re: balances

Fri, 27 Mar 2015 10:46:56 GMT

Give me a clearer scenario

Re: balances

Fri, 27 Mar 2015 12:26:42 GMT

When i placed this expression on drill down Year>>Month it couldn't carry Dec closing balance to Jan

Re: balances

petter — Fri, 27 Mar 2015 12:30:05 GMT

Well the drill-down essentially removes the year ... so then you will have to have a combined YearMonth dimension as I mentioned earlier ...

Re: balances

Fri, 27 Mar 2015 16:53:29 GMT

Thanks Petter,

But I want it to drill down

Re: balances

Fri, 27 Mar 2015 16:55:40 GMT

Yes am using Pivot table and wants it to drill down from Year>>Month Above wont give accurate value. I think Previous will do, but have not gotten the correct syntax

Re: balances

Jacek — Mon, 30 Mar 2015 13:07:42 GMT

I understand Your problem. It isn`t easy. According to your qvw file - table on left with [Above] is good for YEAR and MONTHS except for January.

My proposition:

1) You can count (in resident load) values for each January for all Years (by previous value from December, previous Year).

2) After, in expression of table, make IF condition to January (show counted values), but to others months use [Above]

It will by good if You do not use other filtres than Date.

Regards

Jacek

Re: balances

Wed, 08 Apr 2015 14:54:45 GMT

My issues persists