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

[IAMDV]

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

Cheers - DV

[ulyxess1983]

Here you go, DV. Thanks for quick reply!

[IAMDV]

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

Cheers - DV

[ulyxess1983]

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

[IAMDV]

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

Thanks again.

Cheers - DV

[IAMDV · Accepted solution]

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)

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

[ulyxess1983]

Thanks, sounds great.

How should I set variables - in script?

[IAMDV]

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

[IAMDV]

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