Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
please select a particular month of your Yearmonth field.
then get the disered result.
TRY THIS
=money(fabs(if([Exec P&L Level] = 's','',Sum({$<YearMonth = {"$(=Date([YearMonth],'YY MMM'))"}, month = ,year=>}[Amount])), $(vCurrency)))
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
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.
Hi er
can you attach the QV doc , i still get null result.
Paul
See the attached file
Hi er
Thank you for your example , Any reasons it does work for my case ?
Paul
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
please select a particular month of your Yearmonth field.
then get the disered result.
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 ?