topic Problem with Balance Sheet in QlikView

Problem with Balance Sheet

hpwinkelmann — Tue, 07 Sep 2010 11:07:12 GMT

Hello Everbody,

After a while I managed to set up a balance sheet in QV. But I still have problem with the bank accounts. These accounts can change between assets and liabilities according to the account total at the selected date.

Each account now have a POSITION1 and POSITION2.

The point now is that I tried to set up a dynamic dimension which should choose between POSITION1 and POSITION2 in relation with the account total.

If(sum(AMOUNT)<0,POSITION1,POSITION2)

It seems that dynamic dimensions can not contain the function sum().

Does anybody has an idea how to handle accounts changing the balance sheet side?

Thanks for your help!

Problem with Balance Sheet

Tue, 07 Sep 2010 11:34:51 GMT

Hi,

When you use aggregate functions in calculated dimension, you should group the aggregate fucntion (i.e SUM) with dimensions.

Use Aggr() function to group your sum with dimensions.

- Sridhar

Problem with Balance Sheet

Tue, 07 Sep 2010 12:25:02 GMT

Hi,

For example, you wanted to classify two columns Debit and Credit. If Sum of the GLAmount is negative then it should come under Credit column else should come under Debit Column.

You can classify two seperate expressions for these.

Regards,

Raj Kishor

AW:Re: Problem with Balance Sheet

hpwinkelmann — Tue, 07 Sep 2010 13:02:26 GMT

Hello Sridhar,

I tested your advise, but I didn't manage it.

I have two tables: First one with ACCOUNT, AMOUNT, PERIOD and second one with ACCOUNT, POSITION1, POSITION2

My dimension is: If(sum(AMOUNT)<0,POSITION1,POSITION2)

My expression is: sum(AMOUNT)

How do I have to modify my dimension to avoid an error?

Hans-Peter

AW:Re: Problem with Balance Sheet

hpwinkelmann — Tue, 07 Sep 2010 13:05:40 GMT

Hello Raj,

Thanks for your answer.

I tried this before, but the problem stays the same. I need a dynamic dimension which selects the right balance sheet position.

In my answer to Sridhar you can see the data structur and my dimension und expression.

Hans-Peter

AW:Re: Problem with Balance Sheet

Tue, 07 Sep 2010 14:18:28 GMT

Hi,

your expression should be as follows.

if( aggr(sum(AMOUNT),FIELDNAME)<0,POSITION1,POSITION2)

on the place of FIELDNAME, you give the field name on with respect to which you want to sum up the value and check it to < 0.

Your expression should be as below.

if( aggr(sum(AMOUNT),POSITION1)<0,POSITION1,POSITION2)

if( aggr(sum(AMOUNT),POSITION2)<0,POSITION1,POSITION2)

Hope this may help you.

- Sridhar

AW:Re: AW:Re: Problem with Balance Sheet

hpwinkelmann — Wed, 08 Sep 2010 07:23:38 GMT

Thanks very much Sridhar.

I made a big step foreward!!!