Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ali_hijazi
Partner - Master II
Partner - Master II

Help with trial balance expression

Hello got a QVF file in which I'm creating a pivot table for trial balance
the result is 100% correct (open attached qvf and select the company code 010 and select A/P-A/R accounting)
You see the opening balance, the activity, and closing balance and how the opening balance of a month is the closing balance of the previous month...
Now if you select Nov 2010 the numbers are still correct however I can still the remaining months to the right
and that's because of the variable I'm using which is $(vL.TrialBalance.OpeningBalance) which I'm using in the expressions for opening balance, and closing balance

is there a way to hide the months and show only selected ones?

Kindly advise ( @sunny_talwar , @rwunderlich )

I can walk on water when it freezes
16 Replies
sunny_talwar

Okay I see it now... but why are we making Oct 2011 to be 0 again? is it because it is start of a new year for 010? Same will happen in Oct 2012?

ali_hijazi
Partner - Master II
Partner - Master II
Author

yes the fiscal year for company 010 starts every october  other companies may have the start date of the fiscal year on each January

I can walk on water when it freezes
ali_hijazi
Partner - Master II
Partner - Master II
Author

just keep in mind that user may select any month year
he may select Sep 2010 and Oct 2011 and so on...

I can walk on water when it freezes
sunny_talwar

Check attached

ali_hijazi
Partner - Master II
Partner - Master II
Author

Hello @sunny_talwar 
fine if you don't select any month
but when you select Oct 2011 the opening balance is no more zero which is wrong

it is in the variable $(vL.TrialBalance.OpeningBalance) that I'm getting every thing before the current month in the dimension

kindly advise

I can walk on water when it freezes
sunny_talwar

I am not entirely sure I understand what you are looking to get.

for Company code 010, would you always want to see 0 for Oct 2011 because it's first month is Oct 2010? or does it depend on selection? For example, if you select Dec 2010 for 010, would you want to still see 0s in Oct 2011 or would it not be Dec 2011?

ali_hijazi
Partner - Master II
Partner - Master II
Author

for Company 010 every October the revenue and expenses should be zero (the opening balance) for the remaining months it is the cumulative sum for everything before that month

there are 3 measures on the pivot table
Opening Balance = sum of everything before that month
Activity = sum of amounts in that month
Closing Balance = Activity + Opening Balance

the only problem is in the Opening Balance for accounts under Revenue and Expenses the opening balance should be zero in October while for other months it is the sum of all months before that month in a particular fiscal year

I can walk on water when it freezes