Transcription
QuickBooksAdvancedReportingUserGuideby Angela Browne and the QuickBooks Advanced Reporting teamVersion 1.1August 2015
ight tatesandothercountries.Qlik andQlikView rightedmaterialoftheirrespectiveauthors.QuickBooks Advanced Reporting User Guideii
CHAPTER ksAdvancedReportingQlikView Resources222CHAPTER ibrary4445DateFindthehelpcenter58CHAPTER eportAddacustomobject99910CHAPTER hvariableexampleListofpre- ‐builtexpressionsyntaxCHAPTER 22223242424QuickBooks Advanced Reporting User Guidei
CHAPTER 1Welcome to QuickBooks Reporting!We’regladyou’rehere.About QuickBooks Advanced sQlikView ResourcesQlikView dReporting.We’vemadeitsoQlikView kView .PleasenotethatnotalltheQlik alysisWizard/QlikView- ‐SetAnalysis Wizard and Generator.aspx?sa QlikView /products/qlikview/demo- re/resources/how- ‐to- ‐videos?language ik.com/community/qlikview/new- ‐to- ‐qlikviewQuickBooks Advanced Reporting User Guide2
QlikView ty/qlikview/resource- ‐libraryQuickBooks Advanced Reporting User Guide3
CHAPTER 2Learn the basicsGive a user access to Advanced min.2UndertheCompanymenu,selectUsers urselectedrole.7UnderAreaandActivities,selectFile vel,clickFull.9ClickOKOpen up a report template or create your own from data.QuickBooks Advanced Reporting User Guide4
The LibraryTheLibrarycontainsmanypre- le- ‐clickanobjecttoaddittothereport.! eport! and! eryourreportsbydate(seeDate)! e/Field”column.Date Library ObjectDescriptionTable/Field it’smade teformultipletableOther objects musthave a Dimensionthat’s built with fieldsfrom this cal rQuickBooks Advanced Reporting User Guide5
Canonical dYearCustomer r LastInvoiced (M/Y)Customer:Job.ShowsMonthandYear.Customer LastInvoiced stomer:Job.ShowsQuarterandYear.Customer ymentmadeonaCustomer:Job.Customer LastPayment (M/Y)Customer:Job.ShowsMonthandYearCustomer LastPayment howsQuarterandYear.TransactionTransaction transactions.ShowstableMonthandYearTransaction oks Advanced Reporting User GuideQuarterYearfieldTransactionDateCalendartable6
QuarterandYear.Transaction nsaction LastInvoiced (M/Y)ShowslastinvoicedforMonthandYear.Transaction LastInvoiced eldShowslastinvoicedforQuarterandYear.Transaction action LastPayment (M/Y)lastpaymentforMonthandYearTransaction LastPayment fieldlastpaymentforQuarterandYearTransaction ction LastShip MonthandQuickBooks Advanced Reporting User Guide7
Year.Transaction LastShip .Find the help ht,clickthe(?).QuickBooks Advanced Reporting User Guide8
CHAPTER 3Use dit an object in a report1Right- tthetoptoedittheobjectDelete an object in a report1Right- ght- tion.2ClickProperties.3Clickthe electmorethanoneobjectusing CTRL click.QuickBooks Advanced Reporting User Guide9
6Click Delete.78Click OKtoclosethewindow.Add a custom object1Right- Object.3Selecttheobjectyou’dliketoadd.QuickBooks Advanced Reporting User Guide10
CHAPTER 4Expressions and ckBooks Advanced Reporting User Guide11
yntaxforBalanceisifyoulookunderDefinitionandclick[ �or“Thebalanceformycustomer.”Create a chart1Right- ject.3SelectChart.QuickBooks Advanced Reporting User Guide12
ooks Advanced Reporting User Guide13
examplesoyoucanseeit.10 uickBooks Advanced Reporting User Guide14
11 ��llselectTransactions.CustomerFullName.QuickBooks Advanced Reporting User Guide15
12 ClickAdd.13 ClickNext.14 15 FromtheTablemenu,selectTransactions.16 FromtheFieldMenu,selectTransactions.Active.17 ClickPaste.18 ClickOKtosave.QuickBooks Advanced Reporting User Guide16
19 rt.Expressions we’ve created for writetheseExpressionsyourself.Here’s where you can find them1Right- ressionswindow.! ClicktheDimensionsorExpressionstab! SelectanyDimensionorExpression. Dimensions:ClickEdit. Expressions:Double- ‐clickExpressionsnameorclick[ ]underDefinition.QuickBooks Advanced Reporting User Guide17
QuickBooks Advanced Reporting User Guide18
Understanding how to use tdatawasusedtoputittogether. naset.IncludesvExprvariables. osomethinglikeadate,etc ion.o theexpressioneachtime.QuickBooks Advanced Reporting User Guide19
Set Analysis with variable exampleList of pre-built expression syntax . . months.ThisisusedintheLibraryobjectSales- ‐Top10item- ‐last12months. ofthecurrentselection.QuickBooks Advanced Reporting User Guide20
o Forexample,ifyouuseitsum({ (vExprMaxYear) hantwoyears,thentheamountforthelatestyearshows. nthRollingisjustaspecificimplementationofit. eexpression vExprPreviousNMonths(0)pointstotheJune2013. vExprPreviousNQuarters:SameasvExprPreviousNMonths. vExprPreviousNYears:SameasvExprPreviousNMonths. ourdataonlyfornon- ‐pendingsalestransactions. . vExprYearRangeFilter:SameasvExprMonthRangeFilter. dinvExprSales). (asexpressedinvExprSales). ItgoeshandinhandwithvExprPreviousNMonths.Ifyouuse shoulduse hYear]inyourdimension. ucanuse[QuarterYear]. [Year].QuickBooks Advanced Reporting User Guide21
CHAPTER 5Master CalendarWhat is a Master intotheLibraryunderDate tiontypes.Let’s walk through how to use dates like this in a simple report that shows transaction date for Month/Year:1Right- ect Chart.3ClickthePivotTablebutton.QuickBooks Advanced Reporting User Guide22
mtheShowFieldsfromTabledrop- ‐down.8ClickAdd.9ClickNext.10 IntheEditExpressionwindow,typesum().11 Clickbetweentheparenthesessoyourcursorisin- ‐between(and).12 p- ‐down.13 IntheFielddrop- ‐down,selectTransactions.AmountWithSign.14 Here’sanexample.15 Modifytheexpressiontoread:sum({ [Transactions.HeaderOrLine] {Header} }[Transactions.AmountWithSign])! hetransactionamount.16 ClickOK.17 ywerecreated.What if you wanted to see invoices, bills, or jobs based on their lastpayment ollowingtables: ickBooks Advanced Reporting User Guide23
fthistableistheJobActivitiesOverTimetemplate.What if you wanted to see Sales Orders or Customers based on theirlast invoiced ethefollowingtables: esOrder. accordingtothedateoftheirlatestinvoice.What if you wanted to see Sales Orders based on their last ndarsusethefollowingtable: dingtothelastshippeddates.What if you wanted to see transactions based on more than .QuickBooks Advanced Reporting User Guide24
Let’s look at an Canonical date example that you can find in the r.MonthYear.It’susedwiththeexpressions: Invoiced:(sum({ DateBridge.DateType {'TransactionDate'},[Transactions.TxnType] {'Invoice'},[Transactions.HeaderOrLine] {'Header'},Transactions.Pending {0} }[Transactions.AmountWithSign]) Paid:sum({ DateBridge.DateType {'LastPaymentDate'},[Transactions.TxnType] {'Invoice'},Transactions.Paid {1},[Transactions.HeaderOrLine] {'Header'} oteofintheseexpressions: ��and“SalesOrderCreatedvsSalesOrderShipped.”. hetransactionamount.QuickBooks Advanced Reporting User Guide25
QuickBooks Advanced Reporting User Guide26
QuickBooks Advanced Reporting User Guide 2 ! CHAPTER 1 Welcome to QuickBooks Advanced Reporting re)glad)you're)here.) About QuickBooks Advanced Reporting in)better) insights)into)your)business.)
