Hello everyone, i have been dealing with a BAQ but haven´t been able to figure it out how to solve it.
What I need to do is to gather the open balance, debit, credit and balance amount for each GLAccount, based on the “MAYOR” account segment.
as for now, I´ve been capable of gathering the information for each one of the fiscal periods, but the thing is that I need to have the “FiscalPeriod = 0” balance amount as the “openAmount” for “FiscalPeriod = 1” and so on.
attached is a copy of what I have been working with.
with [CUENTAS] as
(select
[GLPeriodBal].[Company] as [GLPeriodBal_Company],
[GLPeriodBal].[BookID] as [GLPeriodBal_BookID],
[GLPeriodBal].[FiscalYear] as [GLPeriodBal_FiscalYear],
[GLPeriodBal].[FiscalPeriod] as [GLPeriodBal_FiscalPeriod],
[GLPeriodBal].[SegValue1] as [GLPeriodBal_SegValue1],
[GLPeriodBal].[BalanceAcct] as [GLPeriodBal_BalanceAcct],
[COASegValues].[SegmentDesc] as [COASegValues_SegmentDesc]
from Erp.GLPeriodBal as GLPeriodBal
inner join Erp.GLAccount as GLAccount on
GLPeriodBal.Company = GLAccount.Company
and GLPeriodBal.SegValue1 = GLAccount.SegValue1
inner join Erp.COASegValues as COASegValues on
GLAccount.SegValue1 = COASegValues.SegmentCode
where (GLPeriodBal.BookID = 'MXN' and GLPeriodBal.FiscalYear = 2015)
group by [GLPeriodBal].[Company],
[GLPeriodBal].[BookID],
[GLPeriodBal].[FiscalYear],
[GLPeriodBal].[FiscalPeriod],
[GLPeriodBal].[SegValue1],
[GLPeriodBal].[BalanceAcct],
[COASegValues].[SegmentDesc])
select
[CUENTAS].[GLPeriodBal_Company] as [GLPeriodBal_Company],
[CUENTAS].[GLPeriodBal_FiscalYear] as [GLPeriodBal_FiscalYear],
[CUENTAS].[GLPeriodBal_FiscalPeriod] as [GLPeriodBal_FiscalPeriod],
[CUENTAS].[GLPeriodBal_SegValue1] as [GLPeriodBal_SegValue1],
[CUENTAS].[GLPeriodBal_BalanceAcct] as [GLPeriodBal_BalanceAcct],
[CUENTAS].[COASegValues_SegmentDesc] as [COASegValues_SegmentDesc],
(((select
(sum( SALDO_INICIAL.OpenBalance )) as [Calculated_SALDO_INICIAL]
from Erp.GLPeriodBal as SALDO_INICIAL
where SALDO_INICIAL.BookID = CUENTAS.GLPeriodBal_BookID and SALDO_INICIAL.FiscalYear = CUENTAS.GLPeriodBal_FiscalYear and SALDO_INICIAL.SegValue1 = CUENTAS.GLPeriodBal_SegValue1 and SALDO_INICIAL.FiscalPeriod = CUENTAS.GLPeriodBal_FiscalPeriod))) as [Calculated_OPEN_BALANCE],
(((select
(sum( DEBIT.DebitAmt )) as [Calculated_DEBIT]
from Erp.GLPeriodBal as DEBIT
where DEBIT.BookID = CUENTAS.GLPeriodBal_BookID and DEBIT.FiscalYear = CUENTAS.GLPeriodBal_FiscalYear and DEBIT.FiscalPeriod = CUENTAS.GLPeriodBal_FiscalPeriod and DEBIT.SegValue1 = CUENTAS.GLPeriodBal_SegValue1))) as [Calculated_DEBIT],
(((select
(sum( CREDIT.CreditAmt )) as [Calculated_CREDIT]
from Erp.GLPeriodBal as CREDIT
where CREDIT.BookID = CUENTAS.GLPeriodBal_BookID and CREDIT.FiscalYear = CUENTAS.GLPeriodBal_FiscalYear and CREDIT.FiscalPeriod = CUENTAS.GLPeriodBal_FiscalPeriod and CREDIT.SegValue1 = CUENTAS.GLPeriodBal_SegValue1))) as [Calculated_CREDIT],
(((select
(sum( SALDO_FINAL.BalanceAmt )) as [Calculated_SALDO_FINAL]
from Erp.GLPeriodBal as SALDO_FINAL
where SALDO_FINAL.BookID = CUENTAS.GLPeriodBal_BookID and SALDO_FINAL.FiscalYear = CUENTAS.GLPeriodBal_FiscalYear and SALDO_FINAL.FiscalPeriod = CUENTAS.GLPeriodBal_FiscalPeriod and SALDO_FINAL.SegValue1 = CUENTAS.GLPeriodBal_SegValue1))) as [Calculated_BALANCE_AMNT]
from CUENTAS as CUENTAS
where (CUENTAS.GLPeriodBal_BookID = 'MXN' and CUENTAS.GLPeriodBal_SegValue1 = '1120121')
and this is what I´m getting as result, which is partially correct:
Company Fical Year Fiscal Period Segment Value 1 Balance Account Description OPEN_BALANCE DEBIT CREDIT BALANCE_AMNT GSW01 2015 0 1120121 1120121-00-010-000-00 102.01 611759.65 0 0 611759.65 GSW01 2015 1 1120121 1120121-00-010-000-00 102.01 0 458452.88 -326901.27 131551.61 GSW01 2015 2 1120121 1120121-00-010-000-00 102.01 0 442306.84 -438123.36 4183.48 GSW01 2015 3 1120121 1120121-00-010-000-00 102.01 0 468788.48 -436076.72 32711.76 GSW01 2015 4 1120121 1120121-00-010-000-00 102.01 0 452980 -421332.6 31647.4 GSW01 2015 5 1120121 1120121-00-010-000-00 102.01 0 30458128.15 -30532793.94 -74665.79 GSW01 2015 6 1120121 1120121-00-010-000-00 102.01 0 642108.81 -941242.93 -299134.12 GSW01 2015 7 1120121 1120121-00-010-000-00 102.01 0 640716.12 -571548.64 69167.48 GSW01 2015 8 1120121 1120121-00-010-000-00 102.01 0 647579.4 -579311.94 68267.46 GSW01 2015 9 1120121 1120121-00-010-000-00 102.01 0 644084.2 -1180863.41 -536779.21 GSW01 2015 10 1120121 1120121-00-010-000-00 102.01 0 844606.93 -577874.27 266732.66 GSW01 2015 11 1120121 1120121-00-010-000-00 102.01 0 645648.64 -588256.49 57392.15 GSW01 2015 12 1120121 1120121-00-010-000-00 102.01 0 645136.31 -584743.86 60392.45
As I tried to explain earlier, I need the BALANCE_AMNT from FiscalPeriod 0 to be the OPEN_BALANCE in FiscalPeriod 1, then, FiscalPeriod1 BALANCE_AMNT needs to be the OPEN_BALANCE + DEBIT + CREDIT and then use it as OPEN_BALANCE in the next FiscalPeriod… and so on…
I will be so thankful if someone has an idea on what I´m missing or what I´m doing wrong.
in the attached BAQ file, I´m filtering for a specific year and for a specific GL account, but suddenly those filters should be removed so a Dashboard can be generated based on the BAQ. BALANZA_CONTA_ELEC2.baq (100.4 KB)