topic Simple calculate open and closing balances in QlikView

Simple calculate open and closing balances

Wed, 22 Feb 2012 02:45:06 GMT

Could someone explain the simplest method of calculating open and closing balances by Month for a product if we have the product # ,description and current stock in one table and a series of supply and demand records by due date in another table?

eg ProductA

Current-Stock 200

March Demand 50

March Supply 30 Need to calculate March closing Stock = 200 - 50 + 30 = 180

April Demand 100

April Supply 60 Need to calculate April Closing Stock = 180 - 100 + 60 = 140

etc

I can produce a simple Pivot table with dimension Part and Month and expressions for the Demand and Supply columns but I don't know how to get the relative closing balance?

Many Tks

Dave

Simple calculate open and closing balances

CELAMBARASAN — Wed, 22 Feb 2012 05:05:17 GMT

Hi,

This should be done in load script then it will be easier.

Can you tell me about the table structure?

Celambarasan

Simple calculate open and closing balances

Wed, 22 Feb 2012 05:06:57 GMT

Hi,

Please share sample data in Excel.

Then, it will become more easy and precise to respond.

Re: Simple calculate open and closing balances

Wed, 22 Feb 2012 05:20:58 GMT

Tks Celambarasan

I’m a bit slow with handling this sort of thing

Product File has

Part#

Part-Description

Inventory-File has

Part#

Qty-On-Hand

Supply/Demand-MRP-File has

Part#

Due-Date

Supply/Demand-Indicator

Qty

As per below I can easily get the monthly Demand and Supply numbers but can’t get a running closing Balance

Does this help?

Regards

Dave

Re: Simple calculate open and closing balances

Wed, 22 Feb 2012 05:26:22 GMT

Here is a sample of the output file with the problem highlighted in red

Regards

Dave

Re: Simple calculate open and closing balances

CELAMBARASAN — Wed, 22 Feb 2012 05:33:31 GMT

Hi,

Check my understanding is correct?

Qty-On-Hand is current stock which is the initial one.

If you have data for more than one year(2009,2010,2011) then Qty-On-Hand is the stock during the start at 2009?

Celambarasan

Re: Simple calculate open and closing balances

CELAMBARASAN — Wed, 22 Feb 2012 05:37:02 GMT

Hi,

Then try with this expression

=Alt(Above([End-Bal]),0) + Supply - Demand

[End-Bal] is the Expression name of the End-Bal

Celambarasan

Re: Simple calculate open and closing balances

Wed, 22 Feb 2012 05:37:47 GMT

Correct – Qty on-Hand is the current Inventory always maintained up to date

The Demand/supply records are generated by our MRP system and they will always be in the future

(The Year-Month generated table goes back into history for past movements (for a separate report on another tab) – I could generate a new Year-Month table just for the future periods if required

Tks

Dave

Re: Simple calculate open and closing balances

Wed, 22 Feb 2012 05:52:12 GMT

Sorry Celambarasan – Still don’t get previous Closing balance reflected in next period closing balance – Am I doing something wrong?

Tks

Dave

Re: Simple calculate open and closing balances

CELAMBARASAN — Wed, 22 Feb 2012 05:55:11 GMT

Hi,

Check by using supply and demand within [] square brackets or use the formula u used for Demand and supply.

=Alt(Above([End-Bal]),0) + [Supply] - [Demand]

Celambarasan

Re: Simple calculate open and closing balances

CELAMBARASAN — Wed, 22 Feb 2012 06:05:11 GMT

Hi,

Check with this,

Temp:

Load

Part#,

MonthName(DueDate) as Months,

Supply,

Demand

Resident Supply/Demand Order by DueDate Asc;

Left Join(Temp)

Load

Part#,

Qty-On-Hand as ClosingBalance

Resident Inventory;

Output:

Load

Part#,

Months,

if(Previous(ClosingBalance)<> null(),Previous(ClosingBalance),ClosingBalance)+Supply-Demand as ClosingBalance

Resident Temp:

Drop Table Temp;

Hope it helps

Celambarasan

Re: Simple calculate open and closing balances

Wed, 22 Feb 2012 06:06:02 GMT

Hi Celambarasan

Tried both methods – still have same problem – End-Bal only showing figure for relevant month

Regards

Dave

Re: Simple calculate open and closing balances

Wed, 22 Feb 2012 06:10:14 GMT

Tks Celambarasan

I will try later tonight – I have to go off site for a meeting now

Your help is much appreciated

Regards

Dave

Re: Simple calculate open and closing balances

Thu, 23 Feb 2012 06:20:37 GMT

Tks Celambarasan – tried this but having difficulty with syntax – sorry I’m still not up to speed with Qlikview

Re: Simple calculate open and closing balances

preminqlik — Sun, 27 Oct 2013 19:24:40 GMT

send your sample data of all the required tables.

so that we can try in script..

Re: Simple calculate open and closing balances

Anonymous — Fri, 10 Jun 2016 12:04:35 GMT

Hi David,

This is something that I created some time ago. Maybe it's useful.

Regards,

Filip