topic Re: Closing Balance and opening Balance Issue in QlikView

Closing Balance and opening Balance Issue

swarnendu — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have a issue with Closing balance and opening Balance.

which is

1. when i select the month of opening that will be show me actual correct data

also its show me correct data for other some month which have transaction date for debit and credit balance.

But which month i have no transaction this month i cant track my closing and opening.

Its general logic if we have no transaction a month then previous month closing is this month opening and closing.

2. opening have only one date which is starting date.

Please find my screen shots and if you have any solution then please help me.

Re: Closing Balance and opening Balance Issue

stabben23 — Wed, 22 Nov 2017 11:35:27 GMT

Hi,

could you give us some sample data or a qvw file to play around With?

Re: Closing Balance and opening Balance Issue

Mark_Little — Wed, 22 Nov 2017 11:51:17 GMT

Hi,

As suggested if you could give use some details

What is the set analysis your using for the Expressions?

What is your data like?

It will be possible achieve, but will need more details

Mark

Re: Closing Balance and opening Balance Issue

swarnendu — Wed, 22 Nov 2017 12:12:58 GMT

Please follow my excel file in sheet 1 and sheet 2 if i select sale in list box

Re: Closing Balance and opening Balance Issue

swarnendu — Wed, 22 Nov 2017 12:14:39 GMT

Please follow my excel file in sheet 1 and sheet 2 if i select sales in list box.

Re: Closing Balance and opening Balance Issue

sunny_talwar — Wed, 22 Nov 2017 12:24:25 GMT

I think what we are looking for is a qvw file where we can see what you have right now. It is always good to know what you need, but what you have is equally important (if not more)

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: Closing Balance and opening Balance Issue

stabben23 — Wed, 22 Nov 2017 12:26:45 GMT

Hi,

I don't get it, actually not at all.

Re: Closing Balance and opening Balance Issue

Mark_Little — Wed, 22 Nov 2017 12:41:15 GMT

HI,

I have taken a look at the excel document and this seems to show me what you are currently seeing and what you want to see.

Can you provide an example of the actual data being brought in?

You are going to need to do something like

SUM({DATE={'<=vSelectDate}'})Debit - Credit)

SUM({DATE={'<MONTHSTART(vSelectDate)}'})Debit - Credit)

Mark

Re: Closing Balance and opening Balance Issue

swarnendu — Tue, 28 Nov 2017 05:26:33 GMT

Please see my attach file

I want to calculate my opening in script.

If you have any solution then please give me.

Re: Closing Balance and opening Balance Issue

stabben23 — Tue, 28 Nov 2017 08:26:29 GMT

Hi,

add these into Your script.

if(RowNo()=1 or MasterCode<>peek(MasterCode),Opening,peek(closing)) as Opening_new,
if(RowNo()=1 or MasterCode<>peek(MasterCode), (Opening+Debit)-Credit,Peek(closing)+Debit-Credit) as closing

Re: Closing Balance and opening Balance Issue

stabben23 — Tue, 28 Nov 2017 08:29:28 GMT

you also need to sort the table asc instead of desc.