topic Re: Balance sheet expression can it be using 1 expression for MonthYear ? in QlikView

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

paulyeo11 — Fri, 03 May 2013 04:53:18 GMT

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

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

Fri, 03 May 2013 05:16:51 GMT

TRY THIS

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

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

paulyeo11 — Fri, 03 May 2013 05:25:57 GMT

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

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

er_mohit — Fri, 03 May 2013 05:30:14 GMT

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.

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

paulyeo11 — Fri, 03 May 2013 05:36:07 GMT

Hi er

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

Paul

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

er_mohit — Fri, 03 May 2013 05:40:02 GMT

See the attached file

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

paulyeo11 — Fri, 03 May 2013 05:43:15 GMT

Hi er

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

Paul

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

paulyeo11 — Fri, 03 May 2013 06:20:03 GMT

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

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

Fri, 03 May 2013 07:36:04 GMT

please select a particular month of your Yearmonth field.

then get the disered result.

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

paulyeo11 — Fri, 03 May 2013 07:46:03 GMT

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 ?

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

paulyeo11 — Fri, 03 May 2013 12:47:38 GMT

Can some one help me with one expression , how to display last 14 month amount ?