topic Initial & End balance calculation problem in QlikView

Initial & End balance calculation problem

ulyxess1983 — Mon, 11 Apr 2011 09:52:30 GMT

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/6320.Turnover.zip:550:0]Hi everyone,

I'm new to QV calculations, so need help:

Have an inventory transaction with Date, Qty, Warehouse etc.

Want to calculate opening balance and end balance for date, week, month.

Trying to use such formula:

SUM({$<[DATEPHYSICAL]= {"<=$(=MAX(DATEPHYSICAL))"}>} GROSSWEIGHT).

Does it make sense? I think it's summung whenever date is < max (dates). Wrong? 🙂

Thanks anyway.

Regards,

Michael

Initial & End balance calculation problem

IAMDV — Mon, 11 Apr 2011 09:57:03 GMT

Michael - Please can you post the QV document with some sample data? I can give a go!

Cheers - DV

Initial & End balance calculation problem

ulyxess1983 — Mon, 11 Apr 2011 10:18:49 GMT

Here you go, DV. Thanks for quick reply!

Initial & End balance calculation problem

IAMDV — Mon, 11 Apr 2011 10:24:54 GMT

Michael - Sorry! I can't see any attachment in your previous post.

Cheers - DV

Initial & End balance calculation problem

ulyxess1983 — Mon, 11 Apr 2011 10:40:23 GMT

Strange, it's in original post at the top of thread

Initial & End balance calculation problem

IAMDV — Mon, 11 Apr 2011 10:42:52 GMT

Oops! I am sorry. I am looking at your last post. Let me look into the QV document.

Thanks again.

Cheers - DV

Initial & End balance calculation problem

IAMDV — Mon, 11 Apr 2011 12:01:55 GMT

Hi Michael,

I had just looked into your data. This is definetly possible, however there will be good amount of work in terms of setting up things. I have the follow ing recommendations..

1. Use the variables before using the Set Analysis in Expressions

2. Use the variables like

vDateOpeningBalance = Min(Date(DATEPHYSICAL, "DD/MM/YYYY"))

and

vDateClosingBalance = Max(Date(DATEPHYSICAL, "DD/MM/YYYY"))

The above variables calculate the Min (Open Balance Date) & Max (Closing Balance Date) for any given period. I mean the variables are evaluated everytime the variables are used.

3. Then use the variables in your expressions... something like

sum({$<((Date((DATEPHYSICAL, "DD/MM/YYYY")))= {$(=($(vDateOpeningBalance )))}>} GROSSWEIGHT)

I am assuming that you have Time Dimension like Week, Month, Quarter & Year in your data model. So your expressions calculate the SUM of GROSSWEIGHT for the specific period. I mean if you select the week you will have the same expression showing the Opening Balance for the week. And if you select the Month the values changes based on the same expression.

I hope this make sense...

Good luck!

Cheers - DV

Initial & End balance calculation problem

ulyxess1983 — Mon, 11 Apr 2011 12:16:52 GMT

Thanks, sounds great.

How should I set variables - in script?

Initial & End balance calculation problem

IAMDV — Mon, 11 Apr 2011 12:30:47 GMT

It should give you the same result as if you defined the variable manually in document properties.

"Let" is the keyword, when you need define a variable in the script and you want it to be evaluated at runtime.

OR

You can also use the Variable Overiew window under Settings > Variable Overiew option. Type in the variable Name as mentioned in the above post and use the Max & Min Date expressions WITHOUT "=" (Equal to) Sign. If you use the

= Min(Date(DATEPHYSICAL, "DD/MM/YYYY")) : It evaluates only onces instead of everytime the variable is used.

So please use...

Min(Date(DATEPHYSICAL, "DD/MM/YYYY")) : Without the "=" sign should be the right solution in your case as you wanted to evaluate everytime the variable is called.

This is very important to understand when working with variables.

Good luck!

Cheers - DV

Initial & End balance calculation problem

IAMDV — Mon, 11 Apr 2011 17:22:02 GMT

Michael - I hope you got this working... however, one other member had similar question and I had posted step by step solution on how to use the variable. I thought this will be very useful for you to refer.

Here is the link to the post (Please look into my solution):

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

Cheers - DV

Initial & End balance calculation problem

ulyxess1983 — Mon, 11 Apr 2011 18:09:35 GMT

DV, i have some doubts about this fomula. Here's an example (you can make it by creating Chart-Straight Table with DatePhysical as Dim:

Date Opening Balance Turnover Closing balance

( mean just SUM(QTY_KG))

01.07.2008 12(!!!) 12 12 (!!!)

02.07.2008 32 (!!!) 24 24 (!!!)

Must be

Date Opening Balance Turnover Closing balance

01.07.2008 0 12 12 (ok)

02.07.2008 12 24 32

I can calculate Closing by Opening + column (2) (Turnover), but it's seems that Opening Balance doesn't work properly.

I did everything lik you suggested

Initial & End balance calculation problem

IAMDV — Mon, 11 Apr 2011 21:52:01 GMT

Any chance of sharing your document with sample data? It will be much easier for me to check...

Cheers!

Initial & End balance calculation problem

ulyxess1983 — Tue, 12 Apr 2011 08:18:58 GMT

Here it is.[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/2605.Turnover2.zip:550:0]

Initial & End balance calculation problem

IAMDV — Tue, 12 Apr 2011 13:06:18 GMT

Hi Michael,

You are right! it does not show the right results... It is because of the format of the DATEPHYSICAL Field. This field is in "DD/MM/YYYY HH:MM:SS" and with our expression for the Opening Balance it can't filter only for the first date. Instead it is doing the same as Closing balance (calculating for all the rows).

So I had tweaked the solution to handle the date format. Please follow the below steps..

1. Change the Variable definition

vDateOpeningBalance : Min(DATEPHYSICAL)

2. Change the expression ONLY for Initial Balance

=SUM({$<DATEPHYSICAL = {[$(=($(vDateOpeningBalance)))]}>} WQTY)

Please calculate the data manually or using excel and check if it returns correct values.

I Hope this will resolve the issue. Please keep me posted..

Initial & End balance calculation problem

IAMDV — Tue, 12 Apr 2011 13:15:45 GMT

Michael - Just adding one more point to my previous post.

Your closing balance expression is the calculated closing balance (Not the closing balance for the last day of the selection). That means...

Closing Balance = Opening Balance + Turnover

Not

Balance on the last of the selection!!!

I hope you need the calculated closing and not the last day balance. Please let me know if you dont understand...

Initial & End balance calculation problem

IAMDV — Tue, 12 Apr 2011 15:14:36 GMT

Michael - Have you got chance to fix this issue? I am very keen to see the outcome.

Initial & End balance calculation problem

ulyxess1983 — Wed, 13 Apr 2011 07:05:05 GMT

DV,

Sorry for late answer.

It works ok.

But if I use DatePhysical as dimension - it still doesn't calculate initial balance (for minimal date).

But I assume, I'll fix it myself.

Thanks

Initial & End balance calculation problem

ulyxess1983 — Wed, 13 Apr 2011 07:05:46 GMT

Ok, so you suggest to calculate Closing Balance as Opening + Turnover?

Initial & End balance calculation problem

IAMDV — Wed, 13 Apr 2011 09:27:20 GMT

Hi Michael,

No problem at all. That's little strange, because I can see the minimum balance with the above expression. Do let me know if you need help.

Sorry! I am not suggesting you to calculate closing balance as Opening + Turnover. I mean to say that our present method is calculating with that approach. However we can change the closing day balance as last day's balance (irrespective of opening balance and turnover).

So please feel free to reply back for any further help.