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

[paulyeo11]

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

[#2]

TRY THIS

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

[paulyeo11]

Hi vishwaranjan

Thank you for your advise. I try your approach  like below  :-

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

But it return zero value. where i go wrong ?

Paul

[er_mohit]

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.

[paulyeo11]

Hi er

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

Paul

[er_mohit]

See the attached file

[paulyeo11]

Hi er

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

Paul

[paulyeo11]

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

[#9 · Accepted solution]

please select  a particular month of your Yearmonth  field.

then get the disered result.

[paulyeo11]

Hi Sir

Yes after I select the month and year . I get the result .

But my user requirement is to view the last 14 month data in one table . May I know how to achieve that ?