Initial & End balance calculation problem

[ulyxess1983]

[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

[ulyxess1983]

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

[IAMDV]

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

Cheers!

[ulyxess1983]

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

[IAMDV]

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..

[IAMDV]

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...

[IAMDV]

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

[ulyxess1983]

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

[ulyxess1983]

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

[IAMDV]

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.