How to pick opening balance from the last Month Closing balance

[nevilledhamsiri]

Hi,

Dear All,

I have prepared schedule which carries three dimension & 4 expressions. Closing balance is obtained adding opening balance, contribution & settlements. Once that is completed, when it comes to next Month I need to pick the last Month closing balance as the opening balance of the current Month & so on. This has to be continued in to each months. Please suggest me an expression so as to pick the opening balance from the closing balance of the last Month. Also next month a gain the same expression to be applied to adjust the opening balance. Excel used too given below.

Br_Code	Schedule_Name	DATE	Item_Description	Opening_Balance	Contribution	Settlements	Closing_Balance
RA	ADVANCE	1-Jan-17	RENT	150,000	25,000	10,000
RA	ADVANCE	1-Jan-17	TRAVELLING	100,000	10,000	5,000
RA	ADVANCE	1-Jan-17	ADVANCE_BP	50,000	10,000	5,000
RA	ADVANCE	1-Jan-17	OTHER_ADAVCE	35,000	25,000
RA	ADVANCE	1-Feb-17	RENT			10,000
RA	ADVANCE	1-Feb-17	TRAVELLING			5,000
RA	ADVANCE	1-Feb-17	ADVANCE_BP			5,000
RA	ADVANCE	1-Feb-17	OTHER_ADAVCE			15,000
RA	ADVANCE	1-Feb-17	MAINTENANCE		50,000	10,000
RA	ADVANCE	1-Feb-17	MAINDIFFERED_EXP		100,000	25,000

Thanks in advance

Neville

[joshrussin]

Can you share a sample QV file?

[Frank_Hartmann]

see attached file

hope this helps

[nevilledhamsiri]

Dear Frank,

Sorry I cannot open the qvd as I have personnel edition. Could you please show me the expression & working done!

Thanks

Neville

[Frank_Hartmann]

LOAD * INLINE [
    Br_Code, Schedule_Name, Date, Item_Description, Opening_Balance, Contribution, Settlements, Closing_Balance,
    RA, ADVANCE, 1-Jan-17, RENT, 150000,  25000, 10000,
    RA, ADVANCE, 1-Jan-17, TRAVELLING, 100000,  10000,   5000,
    RA, ADVANCE, 1-Jan-17, ADVANCE_BP,  50000,  10000,   5000,
    RA, ADVANCE, 1-Jan-17, OTHER_ADAVCE,  35000,  25000, ,
    RA, ADVANCE, 1-Feb-17, RENT, , , 10000,
    RA, ADVANCE, 1-Feb-17, TRAVELLING, , ,   5000,
    RA, ADVANCE, 1-Feb-17, ADVANCE_BP, , ,   5000,
    RA, ADVANCE, 1-Feb-17, OTHER_ADAVCE, , , 15000,
    RA, ADVANCE, 1-Feb-17, MAINTENANCE, ,  50000, 10000,
    RA, ADVANCE, 1-Feb-17, MAINDIFFERED_EXP, ,100000, 25000
];

create Pivottable:

dim1 = BR_Code

dim2 = Date

dim3 = Item_Description

Exp1 =

sum(aggr(if(sum(Opening_Balance)>0,

sum(Opening_Balance),

above(sum(Opening_Balance))+above(sum(Settlements))+above(sum(Contribution))),Item_Description,Date))

Exp2 = sum(Contribution)

Exp3 = sum(Settlements)

Exp4 =

sum(aggr(if(sum(Opening_Balance)>0,

sum(Opening_Balance),

above(sum(Opening_Balance))+above(sum(Settlements))+above(sum(Contribution))),Item_Description,Date))+

sum(Contribution)+sum(Settlements)

[nevilledhamsiri · Accepted solution]

Thanks a lot Frank, It works! Hope you will help me in my future inquiries as well!

Neville,

[oscar_ortiz]

Neville,

You do realize that you are supposed to give franky_h79 credit for the correct answer not yourself.