topic Re: Help with trial balance expression in App Development

Help with trial balance expression

ali_hijazi — Tue, 18 Aug 2020 15:14:40 GMT

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 )

Re: Help with trial balance expression

sunny_talwar — Thu, 20 Aug 2020 15:09:36 GMT

Check attached

Re: Help with trial balance expression

ali_hijazi — Thu, 20 Aug 2020 15:58:43 GMT

so you only added avg(1)?
ok this works so good with carry-forward-accounts

what if I want something similar but for non-carry forward accounts that reset on period 1 always

the variable that you fixed calculates the cumulative amount per account prior to the minimum month selected
then I add to it the activity per month to get the closing balance

however some accounts like the ones that start with 6 and 7 I want if I select a month in the middle of the fiscal year to work as the one fixed but gives zero for the starting period

hope you can help here

Re: Help with trial balance expression

sunny_talwar — Wed, 26 Aug 2020 17:53:41 GMT

Would you be able to share a sample where we can see this?

Re: Help with trial balance expression

ali_hijazi — Mon, 31 Aug 2020 07:20:58 GMT

Hi @sunny_talwar
check attached sample
select 010 as company code
now in october 2010 the opening balances are zero
what I want to achieve is to have the revenues and expenses be zero in Octo 2011 and then continue on summing the cumulative

Re: Help with trial balance expression

sunny_talwar — Mon, 31 Aug 2020 12:42:43 GMT

You want Oct 2011 to be 0? but right now I only see Oct 2010, Nov 2010, Dec 2010, Jan 2011 in the chart once I select Company Code 010

Re: Help with trial balance expression

ali_hijazi — Mon, 31 Aug 2020 13:06:46 GMT

Hello @sunny_talwar
check attached

Re: Help with trial balance expression

sunny_talwar — Mon, 31 Aug 2020 13:33:44 GMT

I still don't see Oct 2011?

Re: Help with trial balance expression

ali_hijazi — Mon, 31 Aug 2020 13:54:57 GMT

scroll the chart to the right

Re: Help with trial balance expression

ali_hijazi — Mon, 31 Aug 2020 13:57:54 GMT

Hello @sunny_talwar
if you check the month year filter you have all months for 2 fiscal years
you need to scroll the chart from the bottom to the right to see the remaining months on the chart

Re: Help with trial balance expression

sunny_talwar — Mon, 31 Aug 2020 14:10:38 GMT

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?

Re: Help with trial balance expression

ali_hijazi — Mon, 31 Aug 2020 14:24:43 GMT

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

Re: Help with trial balance expression

ali_hijazi — Mon, 31 Aug 2020 14:28:37 GMT

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

Re: Help with trial balance expression

sunny_talwar — Mon, 31 Aug 2020 15:39:40 GMT

Check attached

Re: Help with trial balance expression

ali_hijazi — Tue, 01 Sep 2020 09:10:51 GMT

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

Re: Help with trial balance expression

sunny_talwar — Tue, 01 Sep 2020 10:23:17 GMT

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?

Re: Help with trial balance expression

ali_hijazi — Tue, 01 Sep 2020 10:33:39 GMT

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