QV Balance Sheet showing wrong VAT (Input & Output... - Qlik Community

Report Inappropriate Content · ‎2015-05-21

I have one query which is pulling Balance sheet amounts from SAP Business One database. The query is giving the correct figures for the rest of the accounts except for the VAT Input refundable account 123600 and VAT Output Payable account 221400. The query sums up totals at Title account level(FatherNum) and the above accounts are the title accounts. Here is the query below and I really look forward to your assistance in rectifying this issue:

---------------------------------------------------------------------------------------------------------------------

SELECT CAST(T0.TransId AS Varchar(30)) AS TransId, CASE WHEN t3.FatherNum IN ('100000', '350000') THEN '-3 OK' ELSE CAST(T0.TransType AS Varchar(30))

END AS TransType, CAST(T0.BaseRef AS VarChar(30)) AS BaseRef, T0.RefDate,T0.Number as Docnum, DATEPART(Month, T0.RefDate) AS JrnMonth, T0.FinncPriod, T1.Account, T1.Debit,

T1.Credit, T1.Debit - T1.Credit AS JrnAmt, ISNULL(T1.SYSCred, 0) AS SysCred, ISNULL(T1.SYSDeb, 0) AS SysDeb, T1.ShortName, T1.Ref1, T1.Ref2, T1.Project,

T1.ProfitCode, T1.TotalVat, T0.ObjType, T2.AbsEntry, T2.F_RefDate,t6.AcctCode as AccountCode,t6.Levels,t6.FormatCode, t3.AcctName AS Name,t3.FatherNum, t3.FatherNum AS Fathernum_4, t4.FatherNum AS Fathernum_3,

t5.FatherNum AS Fathernum_2, t3.GroupMask AS FatherNum_1,t3."Segment_0",t3."Segment_1",t3."Segment_2",

CASE

WHEN t3.FatherNum like '1%'

THEN 'Assets'

WHEN t3.FatherNum like '2%'

THEN 'Liabilities'

WHEN t3.FatherNum like '3%'

THEN 'Capital and Reserves'

End As 'BS Group',

CASE

WHEN T0.FinncPriod = '133' THEN '2020-Dec'

WHEN T0.FinncPriod = '132' THEN '2020-Nov'

WHEN T0.FinncPriod = '131' THEN '2020-Oct'

WHEN T0.FinncPriod = '130' THEN '2020-Sep'

WHEN T0.FinncPriod = '129' THEN '2020-Aug'

WHEN T0.FinncPriod = '128' THEN '2020-Jul'

WHEN T0.FinncPriod = '127' THEN '2020-Jun'

WHEN T0.FinncPriod = '126' THEN '2020-May'

WHEN T0.FinncPriod = '125' THEN '2020-Apr'

WHEN T0.FinncPriod = '124' THEN '2020-Mar'

WHEN T0.FinncPriod = '123' THEN '2020-Feb'

WHEN T0.FinncPriod = '122' THEN '2020-Jan'

WHEN T0.FinncPriod = '121' THEN '2019-Dec'

WHEN T0.FinncPriod = '120' THEN '2019-Nov'

WHEN T0.FinncPriod = '119' THEN '2019-Oct'

WHEN T0.FinncPriod = '118' THEN '2019-Sep'

WHEN T0.FinncPriod = '117' THEN '2019-Aug'

WHEN T0.FinncPriod = '116' THEN '2019-Jul'

WHEN T0.FinncPriod = '115' THEN '2019-Jun'

WHEN T0.FinncPriod = '114' THEN '2019-May'

WHEN T0.FinncPriod = '113' THEN '2019-Apr'

WHEN T0.FinncPriod = '112' THEN '2019-Mar'

WHEN T0.FinncPriod = '111' THEN '2019-Feb'

WHEN T0.FinncPriod = '110' THEN '2019-Jan'

WHEN T0.FinncPriod = '109' THEN '2018-Dec'

WHEN T0.FinncPriod = '108' THEN '2018-Nov'

WHEN T0.FinncPriod = '107' THEN '2018-Oct'

WHEN T0.FinncPriod = '106' THEN '2018-Sep'

WHEN T0.FinncPriod = '105' THEN '2018-Aug'

WHEN T0.FinncPriod = '104' THEN '2018-Jul'

WHEN T0.FinncPriod = '103' THEN '2018-Jun'

WHEN T0.FinncPriod = '102' THEN '2018-May'

WHEN T0.FinncPriod = '101' THEN '2018-Apr'

WHEN T0.FinncPriod = '100' THEN '2018-Mar'

WHEN T0.FinncPriod = '99' THEN '2018-Feb'

WHEN T0.FinncPriod = '98' THEN '2018-Jan'

WHEN T0.FinncPriod = '97' THEN '2017-Dec'

WHEN T0.FinncPriod = '96' THEN '2017-Nov'

WHEN T0.FinncPriod = '95' THEN '2017-Oct'

WHEN T0.FinncPriod = '94' THEN '2017-Sep'

WHEN T0.FinncPriod = '93' THEN '2017-Aug'

WHEN T0.FinncPriod = '92' THEN '2017-Jul'

WHEN T0.FinncPriod = '91' THEN '2017-Jun'

WHEN T0.FinncPriod = '90' THEN '2017-May'

WHEN T0.FinncPriod = '89' THEN '2017-Apr'

WHEN T0.FinncPriod = '88' THEN '2017-Mar'

WHEN T0.FinncPriod = '87' THEN '2017-Feb'

WHEN T0.FinncPriod = '86' THEN '2017-Jan'

WHEN T0.FinncPriod = '85' THEN '2016-Dec'

WHEN T0.FinncPriod = '84' THEN '2016-Nov'

WHEN T0.FinncPriod = '83' THEN '2016-Oct'

WHEN T0.FinncPriod = '82' THEN '2016-Sep'

WHEN T0.FinncPriod = '81' THEN '2016-Aug'

WHEN T0.FinncPriod = '80' THEN '2016-Jul'

WHEN T0.FinncPriod = '79' THEN '2016-Jun'

WHEN T0.FinncPriod = '78' THEN '2016-May'

WHEN T0.FinncPriod = '77' THEN '2016-Apr'

WHEN T0.FinncPriod = '76' THEN '2016-Mar'

WHEN T0.FinncPriod = '75' THEN '2016-Feb'

WHEN T0.FinncPriod = '74' THEN '2016-Jan'

WHEN T0.FinncPriod = '73' THEN '2015-Dec'

WHEN T0.FinncPriod = '72' THEN '2015-Nov'

WHEN T0.FinncPriod = '71' THEN '2015-Oct'

WHEN T0.FinncPriod = '70' THEN '2015-Sep'

WHEN T0.FinncPriod = '69' THEN '2015-Aug'

WHEN T0.FinncPriod = '68' THEN '2015-Jul'

WHEN T0.FinncPriod = '67' THEN '2015-Jun'

WHEN T0.FinncPriod = '66' THEN '2015-May'

WHEN T0.FinncPriod = '65' THEN '2015-Apr'

WHEN T0.FinncPriod = '64' THEN '2015-Mar'

WHEN T0.FinncPriod = '63' THEN '2015-Feb'

WHEN T0.FinncPriod = '62' THEN '2015-Jan'

WHEN T0.FinncPriod = '60' THEN '2014-Dec'

WHEN T0.FinncPriod = '59' THEN '2014-Nov'

WHEN T0.FinncPriod = '58' THEN '2014-Oct'

WHEN T0.FinncPriod = '57' THEN '2014-Sep'

WHEN T0.FinncPriod = '56' THEN '2014-Aug'

WHEN T0.FinncPriod = '55' THEN '2014-Jul'

WHEN T0.FinncPriod = '54' THEN '2014-Jun'

WHEN T0.FinncPriod = '53' THEN '2014-May'

WHEN T0.FinncPriod = '52' THEN '2014-Apr'

WHEN T0.FinncPriod = '51' THEN '2014-Mar'

WHEN T0.FinncPriod = '50' THEN '2014-Feb'

WHEN T0.FinncPriod = '49' THEN '2014-Jan'

WHEN T0.FinncPriod = '48' THEN '2013-Dec'

WHEN T0.FinncPriod = '47' THEN '2013-Nov'

WHEN T0.FinncPriod = '46' THEN '2013-Oct'

WHEN T0.FinncPriod = '45' THEN '2013-Sep'

WHEN T0.FinncPriod = '44' THEN '2013-Jun'

WHEN T0.FinncPriod = '41' THEN '2013-May'

WHEN T0.FinncPriod = '40' THEN '2013-Apr'

WHEN T0.FinncPriod = '39' THEN '2013-Mar'

WHEN T0.FinncPriod = '38' THEN '2013-Feb'

WHEN T0.FinncPriod = '37' THEN '2013-Jan'

WHEN T0.FinncPriod = '36' THEN '2012-Dec'

WHEN T0.FinncPriod = '35' THEN '2012-Nov'

WHEN T0.FinncPriod = '34' THEN '2012-Oct'

WHEN T0.FinncPriod = '33' THEN '2012-Sep'

WHEN T0.FinncPriod = '32' THEN '2012-Aug'

WHEN T0.FinncPriod = '31' THEN '2012-Jul'

WHEN T0.FinncPriod = '30' THEN '2012-Jun'

WHEN T0.FinncPriod = '29' THEN '2012-May'

WHEN T0.FinncPriod = '28' THEN '2012-Apr'

WHEN T0.FinncPriod = '27' THEN '2012-Mar'

WHEN T0.FinncPriod = '26' THEN '2012-Feb'

WHEN T0.FinncPriod = '25' THEN '2012-Jan'

WHEN T0.FinncPriod = '24' THEN '2011-Dec'

WHEN T0.FinncPriod = '23' THEN '2011-Nov'

WHEN T0.FinncPriod = '22' THEN '2011-Oct'

WHEN T0.FinncPriod = '21' THEN '2011-Sep'

WHEN T0.FinncPriod = '20' THEN '2011-Aug'

WHEN T0.FinncPriod = '19' THEN '2011-Jul'

WHEN T0.FinncPriod = '18' THEN '2011-Jun'

WHEN T0.FinncPriod = '17' THEN '2011-May'

WHEN T0.FinncPriod = '16' THEN '2011-Apr'

WHEN T0.FinncPriod = '15' THEN '2011-Mar'

WHEN T0.FinncPriod = '14' THEN '2011-Feb'

WHEN T0.FinncPriod = '13' THEN '2011-Jan'

WHEN T0.FinncPriod = '12' THEN '2010-Dec'

WHEN T0.FinncPriod = '11' THEN '2010-Nov'

WHEN T0.FinncPriod = '10' THEN '2010-Oct'

WHEN T0.FinncPriod = '9' THEN '2010-Sep'

WHEN T0.FinncPriod = '8' THEN '2010-Aug'

WHEN T0.FinncPriod = '7' THEN '2010-Jul'

WHEN T0.FinncPriod = '6' THEN '2010-Jun'

WHEN T0.FinncPriod = '5' THEN '2010-May'

WHEN T0.FinncPriod = '4' THEN '2010-Apr'

WHEN T0.FinncPriod = '3' THEN '2010-Mar'

WHEN T0.FinncPriod = '2' THEN '2010-Feb'

WHEN T0.FinncPriod = '1' THEN '2010-Jan'

END AS 'FinPerdCode'

FROM dbo.JDT1 AS T1 INNER JOIN

dbo.OJDT AS T0 ON T0.TransId = T1.TransId LEFT OUTER JOIN

dbo.OFPR AS T2 ON T2.AbsEntry = T1.FinncPriod LEFT OUTER JOIN

dbo.OACT AS t3 ON t3.AcctCode = T1.Account LEFT OUTER JOIN

dbo.OACT AS t4 ON t4.AcctCode = t3.FatherNum LEFT OUTER JOIN

dbo.OACT AS t5 ON t5.AcctCode = t4.FatherNum LEFT OUTER JOIN

dbo.OACT AS t6 ON t6.AcctCode = T1.Account

where t3.Segment_2 = 'LSAL' and t3.GroupMask in ('1','2','3')

--------------------------------------------------------------------------------------------------------------

Report Inappropriate Content · ‎2015-05-21

Below is the script that I am using to pull the data from the query above into the Qlikview Balance Sheet, you check if I might be missing something as well which could be causing the VAT accounts to give wrong amounts.

IF(bType='Blank',0,

IF(bType='Header','',

IF(bDescription='Profit Period',

sum(

{<

TType={'A'},

FinncPriod={">=$(=Max(FinncPriod)-NoOfMonths+1)<=$(=Max(FinncPriod))"},

QTR=,

Year=,

Month=

>}

JrnAmt*-1),

IF(bDescription='Accumulated Surplus / Loss',

sum(

{<

TType={'A'},

FinncPriod={"<=$(=Max(FinncPriod))"},

QTR=,

Year=,

Month=

>}

JrnAmt*-1),

IF(bDescription='Total Capital and Reserves',

$(vAccumulatedProfitLoss)+$(vProfitPeriod)+$(vReserves),

IF(bLineNo=71,

vCapitalReserves+vTotalLiabilities,

sum(

{<

TType={'A'},

FinncPriod={"<=$(=Max(FinncPriod))"},

QTR=,

Year=,

Month=

>}

JrnAmt*-1)))))))

QV Balance Sheet showing wrong VAT (Input & Output) amounts