Problem with Balance Sheet

[hpwinkelmann]

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!

[#2]

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

[#3]

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

[hpwinkelmann]

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

[hpwinkelmann]

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

[#6 · Accepted solution]

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)

or

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

Hope this may help you.

- Sridhar

[hpwinkelmann]

Thanks very much Sridhar.

I made a big step foreward!!!