Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
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

please select  a particular month of your Yearmonth  field.

then get the disered result.

View solution in original post

10 Replies
Not applicable

TRY THIS

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

paulyeo11
Master
Master
Author

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
Master II
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.

paulyeo11
Master
Master
Author

Hi er

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

Paul

er_mohit
Master II
Master II

See the attached file

paulyeo11
Master
Master
Author

Hi er

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

Paul

paulyeo11
Master
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

please select  a particular month of your Yearmonth  field.

then get the disered result.

View solution in original post

paulyeo11
Master
Master
Author

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 ?