# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
cancel
Showing results for
Did you mean:
Master

## Balance sheet expression can it be using 1 expression for MonthYear ?

Hi All

I have a GL Table for Balance Sheet which display correct result.

GL_CODE.. ......................................Aug 2012.......................Sep-2012

10000          CAPITAL          ...........................\$878900000.00.....          \$878900000.00

10030          RETAINED EARNINGS...\$0.00          .......................\$0.00

20006          MOTOR VEHICLES.........\$192784225.00.....          \$192784225.00

.

For Aug 2012 expression as below :-

=money(fabs(if([Exec P&L Level] = 's','',Sum({\$<YearMonth = {"<=\$(=Date(addmonths(Max({\$}[YearMonth]),-8),'YY MMM'))"}, month = ,year=>}[Amount])), \$(vCurrency)))

For Sept 2012 expression as below :-

=money(fabs(if([Exec P&L Level] = 's','',Sum({\$<YearMonth = {"<=\$(=Date(addmonths(Max({\$}[YearMonth]),-7),'YY MMM'))"}, month = ,year=>}[Amount])), \$(vCurrency)))

The reasons i need to use addmonths , is because for Balance GL_DATA , i need to sum up all the data till Sept 2012 data. in order to get the Balance sheet amount for Sept 2012.

My issue of above approach is :-

1. Each column i need a different expression.

2. For 12 column report i need 12 different expression.

3. if i need to make change of expression formula , i need to change 12 expression.

May i know , it there any way i can change my data structure , so that i can make use one expression to get my balance sheet amount ?

Enclsoed my sample QV doc for your reference.

Paul

1 Solution

Accepted Solutions
Not applicable

then get the disered result.

10 Replies
Not applicable

TRY THIS

=money(fabs(if([Exec P&L Level] = 's','',Sum({\$<YearMonth = {"\$(=Date([YearMonth],'YY MMM'))"}, month = ,year=>}[Amount])), \$(vCurrency)))

Master
Author

Hi vishwaranjan

Sum({\$<YearMonth = {"\$(=Date([YearMonth],'YY MMM'))"}, month = ,year=>}[Amount]))

But it return zero value. where i go wrong ?

Paul

Master II

Try this

=money(fabs(if([Exec P&L Level] = 's','',sum({<[YearMonth] = {">=\$(=MonthStart(Max([YearMonth]), -3))<=\$(=MonthEnd(Max([YearMonth]), -1))"},year=,month=>}[Amount])),\$(vCurrency)))

here you change the 3 for hoe long u see the data

currently it shows for 3month starts from your max month

on selection it will show to 3month as per your selection of 1month.

Master
Author

Hi er

can you attach the QV doc , i still get null result.

Paul

Master II

See the attached file

Master
Author

Hi er

Thank you for your example , Any reasons it does work for my case ?

Paul

Master
Author

Hi All

Can some one advise me where I go wrong on the 2 reply from user here is not working for my qv doc ?

Paul

Not applicable