Self Service Analytics
In the previous chapter, we learned how to extract, load and transform data. Often, ERP system use RDBMS database to store data in normalized forms. However, recently, due to advancements in cloud computing, ELT Data lakes gained popularity to perform data analysis, high performance parallel computing in cloud environments.
In this chapter, we will learn how to create self-service normalized data for ad-hoc reporting using The Julia Language. We will create sample data to mimic actuals datasets and will further create basic GL reports using this data.
This tpye of drag 'n' drop ad-hoc analytics reporting is very popular using BI tools like Microsoft Power BI, Tableau, Kibana, OACs, Cognos etc.
Creating these basic reports in Julia language may not look very useful in the beginning. This exercise should be seen as creating fundamental environment for reporting, which helps perform Advance Analytics, Real time analytics, Advance Visualizations and Predictive analytics on Financial data later on.
About ERP Systems, General Ledger & Supply chain
A typical ERP system consists of many modules based on business domain, functions and operations.
GL is core of Finance and Supply chain domains and Buy to Pay, Order to Cash deal with different aspects of business operations in an Organization. Many organization, use ERPs in different ways and may chose to implement all or some of the modules.
You can find examples of module specific business operations/processes diagram here.
- General Ledger process flow
- Account Payable process flow
- Tax Analytics
- Sample GL ERD - Entity Relaton Diagram
A typical ERP modules list looks like below diagram.
Current Solutions
Big Organizations have been using big ERP systems like SAP, Oracle, PeopleSoft, Coupa, Workday etc. systems over few decades now and Recent popularity of softwares like Quickbooks, NetSuite, Tally in medium, small organizations are proof that ERP are the way to manage any business successfully.
Finance analysts, supply chain managers heavily rely on using Business Intelligence tools like Microsoft Excel, Microsoft Power BI, Tableau, Oracle Analytics, Google Analytics, IBM Cognos, Business Objects etc.
These BI tools provide a self-service reporting for analytics and often are used for managing daily ad-hoc reporting and anlysis.
A typical ERP data flow process looks like below diagram.
Problem Statement
"Read, Write and Understand" data are three aspects of any ERP system.
While big and small ERPs master "write aspect" of ERP, there is lot needs to be done on "read & understand" data.
I would rather not waste your time talking about how one BI Tools compare with Pluto or others,
instead, in this chapter, I will show some sample reports I built in Pluto last year for Pandemic reporting, and then let Analysts decide, if They would have rather used Traditional BI reportings tools to build these reports.
Point is, How easily, Pluto can create real time ad-hoc, Reactive dashboard analytics to support critical business operations.
understanding Finance, Supply chain data
A typical Finance statement look like this. click here
Let's first create chartfields to support General Ledger finance books accounting structure, this accounting structure will support Ledger Analytics and should be interpreted as data structure responsilbe to create Finance Statements.
Examples
below are sample data sets,
Accounts, Dept (or Cost Center), Location, and Finance Ledger may look like below examples.
Let's first activate GeneralLedger.jl package.
julia> # Let's first import GeneralLedger.jl package # in following examples, I'll assume, that you downloaded this package using Pkgjulia> Pkg.add(url="https://github.com/AmitXShukla/GeneralLedger.jl")Updating git-repo `https://github.com/AmitXShukla/GeneralLedger.jl` ERROR: package `GeneralLedger [640f5719]` has same name or UUID as the active projectjulia> using GeneralLedger
Accounts Dimension
julia> using DataFrames, Datesjulia> # create dummy data accounts = DataFrame(AS_OF_DATE=Date("1900-01-01", dateformat"y-m-d"), ID = 11000:1000:45000, CLASSIFICATION=repeat([ "OPERATING_EXPENSES","NON-OPERATING_EXPENSES", "ASSETS","LIABILITIES", "NET_WORTH","STATISTICS","REVENUE" ], inner=5), CATEGORY=[ "Travel","Payroll","non-Payroll","Allowance","Cash", "Facility","Supply","Services","Investment","Misc.", "Depreciation","Gain","Service","Retired","Fault.", "Receipt","Accrual","Return","Credit","ROI", "Cash","Funds","Invest","Transfer","Roll-over", "FTE","Members","Non_Members","Temp","Contractors", "Sales","Merchant","Service","Consulting","Subscriptions" ], STATUS="A", DESCR=repeat([ "operating expenses","non-operating expenses", "assets","liability","net-worth","stats","revenue" ], inner=5), ACCOUNT_TYPE=repeat([ "E","E","A","L","N","S","R" ],inner=5));julia> accounts[collect(1:5:35),:]7×7 DataFrame Row │ AS_OF_DATE ID CLASSIFICATION CATEGORY STATUS DESCR ⋯ │ Dates.Date Int64 String String String String ⋯ ─────┼────────────────────────────────────────────────────────────────────────── 1 │ 1900-01-01 11000 OPERATING_EXPENSES Travel A operat ⋯ 2 │ 1900-01-01 16000 NON-OPERATING_EXPENSES Facility A non-op 3 │ 1900-01-01 21000 ASSETS Depreciation A assets 4 │ 1900-01-01 26000 LIABILITIES Receipt A liabil 5 │ 1900-01-01 31000 NET_WORTH Cash A net-wo ⋯ 6 │ 1900-01-01 36000 STATISTICS FTE A stats 7 │ 1900-01-01 41000 REVENUE Sales A revenu 2 columns omitted
Department Dimension
julia> using DataFrames, Datesjulia> # create dummy data dept = DataFrame(AS_OF_DATE=Date("2000-01-01", dateformat"y-m-d"), ID = 1100:100:1500, CLASSIFICATION=[ "SALES","HR", "IT","BUSINESS","OTHERS" ], CATEGORY=[ "sales","human_resource","IT_Staff","business","others" ], STATUS="A", DESCR=[ "Sales & Marketing","Human Resource","Infomration Technology","Business leaders","other temp" ], DEPT_TYPE=[ "S","H","I","B","O"]);julia> dept[collect(1:5),:]5×7 DataFrame Row │ AS_OF_DATE ID CLASSIFICATION CATEGORY STATUS DESCR ⋯ │ Dates.Date Int64 String String String String ⋯ ─────┼────────────────────────────────────────────────────────────────────────── 1 │ 2000-01-01 1100 SALES sales A Sales & Mark ⋯ 2 │ 2000-01-01 1200 HR human_resource A Human Resour 3 │ 2000-01-01 1300 IT IT_Staff A Infomration 4 │ 2000-01-01 1400 BUSINESS business A Business lea 5 │ 2000-01-01 1500 OTHERS others A other temp ⋯ 2 columns omitted
Location Dimension
julia> using DataFrames, Datesjulia> # create dummy data location = DataFrame(AS_OF_DATE=Date("2000-01-01", dateformat"y-m-d"), ID = 11:1:22, CLASSIFICATION=repeat([ "Region A","Region B", "Region C"], inner=4), CATEGORY=repeat([ "Region A","Region B", "Region C"], inner=4), STATUS="A", DESCR=[ "Boston","New York","Philadelphia","Cleveland","Richmond", "Atlanta","Chicago","St. Louis","Minneapolis","Kansas City", "Dallas","San Francisco"], LOCA_TYPE="Physical");julia> location[:,:]12×7 DataFrame Row │ AS_OF_DATE ID CLASSIFICATION CATEGORY STATUS DESCR LOC ⋯ │ Dates.Date Int64 String String String String Str ⋯ ─────┼────────────────────────────────────────────────────────────────────────── 1 │ 2000-01-01 11 Region A Region A A Boston Phy ⋯ 2 │ 2000-01-01 12 Region A Region A A New York Phy 3 │ 2000-01-01 13 Region A Region A A Philadelphia Phy 4 │ 2000-01-01 14 Region A Region A A Cleveland Phy 5 │ 2000-01-01 15 Region B Region B A Richmond Phy ⋯ 6 │ 2000-01-01 16 Region B Region B A Atlanta Phy 7 │ 2000-01-01 17 Region B Region B A Chicago Phy 8 │ 2000-01-01 18 Region B Region B A St. Louis Phy 9 │ 2000-01-01 19 Region C Region C A Minneapolis Phy ⋯ 10 │ 2000-01-01 20 Region C Region C A Kansas City Phy 11 │ 2000-01-01 21 Region C Region C A Dallas Phy 12 │ 2000-01-01 22 Region C Region C A San Francisco Phy 1 column omitted
visuals
julia> using DataFrames, Plots, Datesjulia> # create dummy data accounts = DataFrame(AS_OF_DATE=Date("1900-01-01", dateformat"y-m-d"), ID = 11000:1000:45000, CLASSIFICATION=repeat([ "OPERATING_EXPENSES","NON-OPERATING_EXPENSES", "ASSETS","LIABILITIES", "NET_WORTH","STATISTICS","REVENUE" ], inner=5), CATEGORY=[ "Travel","Payroll","non-Payroll","Allowance","Cash", "Facility","Supply","Services","Investment","Misc.", "Depreciation","Gain","Service","Retired","Fault.", "Receipt","Accrual","Return","Credit","ROI", "Cash","Funds","Invest","Transfer","Roll-over", "FTE","Members","Non_Members","Temp","Contractors", "Sales","Merchant","Service","Consulting","Subscriptions" ], STATUS="A", DESCR=repeat([ "operating expenses","non-operating expenses", "assets","liability","net-worth","stats","revenue" ], inner=5), ACCOUNT_TYPE=repeat([ "E","E","A","L","N","S","R" ],inner=5));julia> dept = DataFrame(AS_OF_DATE=Date("2000-01-01", dateformat"y-m-d"), ID = 1100:100:1500, CLASSIFICATION=[ "SALES","HR", "IT","BUSINESS","OTHERS" ], CATEGORY=[ "sales","human_resource","IT_Staff","business","others" ], STATUS="A", DESCR=[ "Sales & Marketing","Human Resource","Infomration Technology","Business leaders","other temp" ], DEPT_TYPE=[ "S","H","I","B","O"]);julia> location = DataFrame(AS_OF_DATE=Date("2000-01-01", dateformat"y-m-d"), ID = 11:1:22, CLASSIFICATION=repeat([ "Region A","Region B", "Region C"], inner=4), CATEGORY=repeat([ "Region A","Region B", "Region C"], inner=4), STATUS="A", DESCR=[ "Boston","New York","Philadelphia","Cleveland","Richmond", "Atlanta","Chicago","St. Louis","Minneapolis","Kansas City", "Dallas","San Francisco"], LOCA_TYPE="Physical");julia> p1 = plot((combine(groupby(accounts, :CLASSIFICATION), nrow)).nrow,(combine(groupby(accounts, :CLASSIFICATION), nrow)).CLASSIFICATION, seriestype=scatter, label = "# of accounts by classification", xlabel = "# of accounts", ylabel="Class", xlims = (0, 5.5))Plot{Plots.GRBackend() n=1}julia> p2 = plot((combine(groupby(dept, :CLASSIFICATION), nrow)).nrow,(combine(groupby(dept, :CLASSIFICATION), nrow)).CLASSIFICATION, seriestype=scatter, label = "# of dept by classification", xlabel = "# of depts", ylabel="Class", xlims = (0, 2))Plot{Plots.GRBackend() n=1}julia> p3 = plot((combine(groupby(accounts, :CLASSIFICATION), nrow)).nrow,(combine(groupby(location, :CLASSIFICATION), nrow)).CLASSIFICATION, seriestype=scatter, label = "# of locations by classification", xlabel = "# of locations", ylabel="Class", xlims = (1, 6.5))Plot{Plots.GRBackend() n=1}julia> plot(p1, p2, p3, layout = (3, 1), legend = false)Plot{Plots.GRBackend() n=3}
Financial Statements
Finance Ledger, Balance Sheet, Income, Cash Flow Statements
below is sample Finance Ledger Data
julia> using DataFrames, Plots, Datesjulia> # create dummy data accounts = DataFrame(AS_OF_DATE=Date("1900-01-01", dateformat"y-m-d"), ID = 11000:1000:45000, CLASSIFICATION=repeat([ "OPERATING_EXPENSES","NON-OPERATING_EXPENSES", "ASSETS","LIABILITIES", "NET_WORTH","STATISTICS","REVENUE" ], inner=5), CATEGORY=[ "Travel","Payroll","non-Payroll","Allowance","Cash", "Facility","Supply","Services","Investment","Misc.", "Depreciation","Gain","Service","Retired","Fault.", "Receipt","Accrual","Return","Credit","ROI", "Cash","Funds","Invest","Transfer","Roll-over", "FTE","Members","Non_Members","Temp","Contractors", "Sales","Merchant","Service","Consulting","Subscriptions" ], STATUS="A", DESCR=repeat([ "operating expenses","non-operating expenses", "assets","liability","net-worth","stats","revenue" ], inner=5), ACCOUNT_TYPE=repeat([ "E","E","A","L","N","S","R" ],inner=5));julia> dept = DataFrame(AS_OF_DATE=Date("2000-01-01", dateformat"y-m-d"), ID = 1100:100:1500, CLASSIFICATION=[ "SALES","HR", "IT","BUSINESS","OTHERS" ], CATEGORY=[ "sales","human_resource","IT_Staff","business","others" ], STATUS="A", DESCR=[ "Sales & Marketing","Human Resource","Infomration Technology","Business leaders","other temp" ], DEPT_TYPE=[ "S","H","I","B","O"]);julia> location = DataFrame(AS_OF_DATE=Date("2000-01-01", dateformat"y-m-d"), ID = 11:1:22, CLASSIFICATION=repeat([ "Region A","Region B", "Region C"], inner=4), CATEGORY=repeat([ "Region A","Region B", "Region C"], inner=4), STATUS="A", DESCR=[ "Boston","New York","Philadelphia","Cleveland","Richmond", "Atlanta","Chicago","St. Louis","Minneapolis","Kansas City", "Dallas","San Francisco"], LOCA_TYPE="Physical");julia> ledger = DataFrame( LEDGER = String[], FISCAL_YEAR = Int[], PERIOD = Int[], ORGID = String[], OPER_UNIT = String[], ACCOUNT = Int[], DEPT = Int[], LOCATION = Int[], POSTED_TOTAL = Float64[] );julia> # create 2020 Period 1-12 Actuals Ledger l = "Actuals";julia> fy = 2020;julia> for p = 1:12 for i = 1:10^5 push!(ledger, (l, fy, p, "ABC Inc.", rand(location.CATEGORY), rand(accounts.ID), rand(dept.ID), rand(location.ID), rand()*10^8)) end endjulia> # create 2021 Period 1-4 Actuals Ledger l = "Actuals";julia> fy = 2021;julia> for p = 1:4 for i = 1:10^5 push!(ledger, (l, fy, p, "ABC Inc.", rand(location.CATEGORY), rand(accounts.ID), rand(dept.ID), rand(location.ID), rand()*10^8)) end endjulia> # create 2021 Period 1-4 Budget Ledger l = "Budget";julia> fy = 2021;julia> for p = 1:12 for i = 1:10^5 push!(ledger, (l, fy, p, "ABC Inc.", rand(location.CATEGORY), rand(accounts.ID), rand(dept.ID), rand(location.ID), rand()*10^8)) end endjulia> ledger[:,:]2800000×9 DataFrame Row │ LEDGER FISCAL_YEAR PERIOD ORGID OPER_UNIT ACCOUNT DEPT ⋯ │ String Int64 Int64 String String Int64 Int64 ⋯ ─────────┼────────────────────────────────────────────────────────────────────── 1 │ Actuals 2020 1 ABC Inc. Region B 32000 1100 ⋯ 2 │ Actuals 2020 1 ABC Inc. Region A 43000 1200 3 │ Actuals 2020 1 ABC Inc. Region B 19000 1400 4 │ Actuals 2020 1 ABC Inc. Region C 45000 1200 5 │ Actuals 2020 1 ABC Inc. Region C 22000 1300 ⋯ 6 │ Actuals 2020 1 ABC Inc. Region C 14000 1200 7 │ Actuals 2020 1 ABC Inc. Region B 38000 1500 8 │ Actuals 2020 1 ABC Inc. Region C 33000 1500 ⋮ │ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋱ 2799994 │ Budget 2021 12 ABC Inc. Region C 27000 1400 ⋯ 2799995 │ Budget 2021 12 ABC Inc. Region C 44000 1300 2799996 │ Budget 2021 12 ABC Inc. Region A 29000 1500 2799997 │ Budget 2021 12 ABC Inc. Region A 22000 1100 2799998 │ Budget 2021 12 ABC Inc. Region A 19000 1400 ⋯ 2799999 │ Budget 2021 12 ABC Inc. Region A 43000 1300 2800000 │ Budget 2021 12 ABC Inc. Region C 32000 1500 2 columns and 2799985 rows omittedjulia> # create default binding values/params using PlutoUIjulia> ## WARNING ## These bind variable will throw in error in documentation ## however, these runs fine on Pluto notebooks and provide a slider to change values dynamically # @bind ld Select(["Actuals", "Budget"]) # @bind rg Select(["Region A", "Region B", "Region C"]) # @bind yr Slider(2020:1:2021, default=2020, show_value=true) # @bind qtr Slider(1:1:4, default=1, show_value=true) # @bind ld_p Select(["Actuals", "Budget"]) # @bind yr_p Slider(2020:1:2021, default=2021, show_value=true) # @bind rg_p Select(["Region A", "Region B", "Region C"]) # @bind ldescr Select(unique(location.DESCR)) # @bind adescr Select(unique(accounts.CLASSIFICATION)) # @bind ddescr Select(unique(dept.CLASSIFICATION)) ld = "Actuals""Actuals"julia> rg = "Region B""Region B"julia> yr = 20202020julia> qtr = 11julia> ld_p = "Actuals""Actuals"julia> rg_p = "Region B""Region B"julia> yr_p = 20202020julia> qtr_p = 11julia> ldescr = unique(location.DESCR)12-element Vector{String}: "Boston" "New York" "Philadelphia" "Cleveland" "Richmond" "Atlanta" "Chicago" "St. Louis" "Minneapolis" "Kansas City" "Dallas" "San Francisco"julia> adescr = unique(accounts.CLASSIFICATION)7-element Vector{String}: "OPERATING_EXPENSES" "NON-OPERATING_EXPENSES" "ASSETS" "LIABILITIES" "NET_WORTH" "STATISTICS" "REVENUE"julia> ddescr = unique(dept.CLASSIFICATION)5-element Vector{String}: "SALES" "HR" "IT" "BUSINESS" "OTHERS"julia> ######################## ##### BALANCE SHEET #### ######################## # rename dimensions columns for innerjoin df_accounts = rename(accounts, :ID => :ACCOUNTS_ID, :CLASSIFICATION => :ACCOUNTS_CLASSIFICATION, :CATEGORY => :ACCOUNTS_CATEGORY, :DESCR => :ACCOUNTS_DESCR);julia> df_dept = rename(dept, :ID => :DEPT_ID, :CLASSIFICATION => :DEPT_CLASSIFICATION, :CATEGORY => :DEPT_CATEGORY, :DESCR => :DEPT_DESCR);julia> df_location = rename(location, :ID => :LOCATION_ID, :CLASSIFICATION => :LOCATION_CLASSIFICATION, :CATEGORY => :LOCATION_CATEGORY, :DESCR => :LOCATION_DESCR);julia> # create a function which converts accounting period to Quarter function periodToQtr(x) if x ∈ 1:3 return 1 elseif x ∈ 4:6 return 2 elseif x ∈ 7:9 return 3 else return 4 end endperiodToQtr (generic function with 1 method)julia> ############################################################## # create a new dataframe to join all chartfields with ledger # ############################################################## df_ledger = innerjoin( innerjoin( innerjoin(ledger, df_accounts, on = [:ACCOUNT => :ACCOUNTS_ID], makeunique=true), df_dept, on = [:DEPT => :DEPT_ID], makeunique=true), df_location, on = [:LOCATION => :LOCATION_ID], makeunique=true);julia> transform!(df_ledger, :PERIOD => ByRow(periodToQtr) => :QTR);julia> function numToCurrency(x) return string("USD ",round(x/10^6; digits = 2), "m") endnumToCurrency (generic function with 1 method)julia> gdf = groupby(df_ledger, [:LEDGER, :FISCAL_YEAR, :QTR, :OPER_UNIT, :ACCOUNTS_CLASSIFICATION, :DEPT_CLASSIFICATION, # :LOCATION_CLASSIFICATION, :LOCATION_DESCR]);julia> gdf_plot = combine(gdf, :POSTED_TOTAL => sum => :TOTAL);julia> select(gdf_plot[( (gdf_plot.FISCAL_YEAR .== yr) .& (gdf_plot.QTR .== qtr) .& (gdf_plot.LEDGER .== ld) .& (gdf_plot.OPER_UNIT .== rg) ),:], :FISCAL_YEAR => :FY, :QTR => :Qtr, :OPER_UNIT => :Org, :ACCOUNTS_CLASSIFICATION => :Accounts, :DEPT_CLASSIFICATION => :Dept, # :LOCATION_CLASSIFICATION => :Region, :LOCATION_DESCR => :Loc, :TOTAL => ByRow(numToCurrency) => :TOTAL)420×7 DataFrame Row │ FY Qtr Org Accounts Dept Loc ⋯ │ Int64 Int64 String String String String ⋯ ─────┼────────────────────────────────────────────────────────────────────────── 1 │ 2020 1 Region B NET_WORTH SALES Chicago ⋯ 2 │ 2020 1 Region B NON-OPERATING_EXPENSES BUSINESS San Francisco 3 │ 2020 1 Region B STATISTICS OTHERS Atlanta 4 │ 2020 1 Region B REVENUE SALES Boston 5 │ 2020 1 Region B LIABILITIES SALES Dallas ⋯ 6 │ 2020 1 Region B LIABILITIES OTHERS San Francisco 7 │ 2020 1 Region B ASSETS IT Kansas City 8 │ 2020 1 Region B REVENUE SALES Minneapolis ⋮ │ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋱ 414 │ 2020 1 Region B NET_WORTH OTHERS Boston ⋯ 415 │ 2020 1 Region B NET_WORTH IT Cleveland 416 │ 2020 1 Region B REVENUE BUSINESS Minneapolis 417 │ 2020 1 Region B NON-OPERATING_EXPENSES IT Minneapolis 418 │ 2020 1 Region B REVENUE HR Boston ⋯ 419 │ 2020 1 Region B OPERATING_EXPENSES HR Cleveland 420 │ 2020 1 Region B ASSETS BUSINESS St. Louis 1 column and 405 rows omittedjulia> ######################## ### Income Statement ### ######################## select(gdf_plot[( (gdf_plot.FISCAL_YEAR .== yr) .& (gdf_plot.QTR .== qtr) .& (gdf_plot.LEDGER .== ld) .& (gdf_plot.OPER_UNIT .== rg) .& (in.(gdf_plot.ACCOUNTS_CLASSIFICATION, Ref(["ASSETS", "LIABILITIES", "REVENUE","NET_WORTH"]))) ),:], :FISCAL_YEAR => :FY, :QTR => :Qtr, :OPER_UNIT => :Org, :ACCOUNTS_CLASSIFICATION => :Accounts, # :DEPT_CLASSIFICATION => :Dept, # :LOCATION_CLASSIFICATION => :Region, # :LOCATION_DESCR => :Loc, :TOTAL => ByRow(numToCurrency) => :TOTAL)240×5 DataFrame Row │ FY Qtr Org Accounts TOTAL │ Int64 Int64 String String String ─────┼──────────────────────────────────────────────────── 1 │ 2020 1 Region B NET_WORTH USD 12225.16m 2 │ 2020 1 Region B REVENUE USD 12643.49m 3 │ 2020 1 Region B LIABILITIES USD 12892.72m 4 │ 2020 1 Region B LIABILITIES USD 10764.71m 5 │ 2020 1 Region B ASSETS USD 12111.64m 6 │ 2020 1 Region B REVENUE USD 12540.19m 7 │ 2020 1 Region B NET_WORTH USD 11007.58m 8 │ 2020 1 Region B NET_WORTH USD 13182.25m ⋮ │ ⋮ ⋮ ⋮ ⋮ ⋮ 234 │ 2020 1 Region B LIABILITIES USD 11106.92m 235 │ 2020 1 Region B NET_WORTH USD 11067.28m 236 │ 2020 1 Region B NET_WORTH USD 13042.37m 237 │ 2020 1 Region B NET_WORTH USD 10276.45m 238 │ 2020 1 Region B REVENUE USD 12719.45m 239 │ 2020 1 Region B REVENUE USD 12698.35m 240 │ 2020 1 Region B ASSETS USD 12186.37m 225 rows omittedjulia> ######################## ##### CASH FLOW ######## ######################## select(gdf_plot[( (gdf_plot.FISCAL_YEAR .== yr) .& (gdf_plot.QTR .== qtr) .& (gdf_plot.LEDGER .== ld) .& (gdf_plot.OPER_UNIT .== rg) .& (in.(gdf_plot.ACCOUNTS_CLASSIFICATION, Ref(["NON-OPERATING_EXPENSES","OPERATING_EXPENSES" ]))) ),:], :FISCAL_YEAR => :FY, :QTR => :Qtr, :OPER_UNIT => :Org, :ACCOUNTS_CLASSIFICATION => :Accounts, # :DEPT_CLASSIFICATION => :Dept, # :LOCATION_CLASSIFICATION => :Region, # :LOCATION_DESCR => :Loc, :TOTAL => ByRow(numToCurrency) => :TOTAL)120×5 DataFrame Row │ FY Qtr Org Accounts TOTAL │ Int64 Int64 String String String ─────┼─────────────────────────────────────────────────────────────── 1 │ 2020 1 Region B NON-OPERATING_EXPENSES USD 11920.44m 2 │ 2020 1 Region B NON-OPERATING_EXPENSES USD 10853.52m 3 │ 2020 1 Region B OPERATING_EXPENSES USD 12810.65m 4 │ 2020 1 Region B OPERATING_EXPENSES USD 12477.57m 5 │ 2020 1 Region B OPERATING_EXPENSES USD 10831.13m 6 │ 2020 1 Region B OPERATING_EXPENSES USD 11898.31m 7 │ 2020 1 Region B NON-OPERATING_EXPENSES USD 11699.14m 8 │ 2020 1 Region B NON-OPERATING_EXPENSES USD 10637.26m ⋮ │ ⋮ ⋮ ⋮ ⋮ ⋮ 114 │ 2020 1 Region B OPERATING_EXPENSES USD 11309.25m 115 │ 2020 1 Region B OPERATING_EXPENSES USD 13624.4m 116 │ 2020 1 Region B OPERATING_EXPENSES USD 12374.09m 117 │ 2020 1 Region B NON-OPERATING_EXPENSES USD 11478.34m 118 │ 2020 1 Region B NON-OPERATING_EXPENSES USD 11197.15m 119 │ 2020 1 Region B NON-OPERATING_EXPENSES USD 11914.0m 120 │ 2020 1 Region B OPERATING_EXPENSES USD 11563.69m 105 rows omittedjulia> ######################## ##### Ledger Visual #### ######################## plot_data = gdf_plot[( (gdf_plot.FISCAL_YEAR .== yr_p) .& (gdf_plot.LEDGER .== ld_p) .& (gdf_plot.OPER_UNIT .== rg_p) .& (gdf_plot.LOCATION_DESCR .== ldescr) .& (gdf_plot.DEPT_CLASSIFICATION .== ddescr) .& (gdf_plot.ACCOUNTS_CLASSIFICATION .== adescr)) , :];ERROR: DimensionMismatch: arrays could not be broadcast to a common size; got a dimension with lengths 12600 and 12julia> # @df plot_data scatter(:QTR, :TOTAL/10^8, title = "Finance Ledger Data", xlabel="Quarter", ylabel="Total (in USD million)", label="$ld_p Total by $yr_p for $rg_p") @df plot_data plot(:QTR, :TOTAL/10^8, title = "Finance Ledger Data", xlabel="Quarter", ylabel="Total (in USD million)", label=[ "$ld_p by $yr_p for $rg_p $ldescr $adescr $ddescr" ], lw=3)ERROR: LoadError: UndefVarError: `@df` not defined in expression starting at REPL[41]:1julia> ################################# ## Actual vs Budget Comparison ## ################################# plot_data_a = gdf_plot[( (gdf_plot.FISCAL_YEAR .== yr_p) .& (gdf_plot.LEDGER .== "Actuals") .& (gdf_plot.OPER_UNIT .== rg_p) .& (gdf_plot.LOCATION_DESCR .== ldescr) .& (gdf_plot.DEPT_CLASSIFICATION .== ddescr) .& (gdf_plot.ACCOUNTS_CLASSIFICATION .== adescr)) , :];ERROR: DimensionMismatch: arrays could not be broadcast to a common size; got a dimension with lengths 12600 and 12julia> # @df plot_data scatter(:QTR, :TOTAL/10^8, title = "Finance Ledger Data", xlabel="Quarter", ylabel="Total (in USD million)", label="$ld_p Total by $yr_p for $rg_p") plot_data_b = gdf_plot[( (gdf_plot.FISCAL_YEAR .== yr_p) .& (gdf_plot.LEDGER .== "Budget") .& (gdf_plot.OPER_UNIT .== rg_p) .& (gdf_plot.LOCATION_DESCR .== ldescr) .& (gdf_plot.DEPT_CLASSIFICATION .== ddescr) .& (gdf_plot.ACCOUNTS_CLASSIFICATION .== adescr)) , :];ERROR: DimensionMismatch: arrays could not be broadcast to a common size; got a dimension with lengths 12600 and 12julia> # @df plot_data scatter(:QTR, :TOTAL/10^8, title = "Finance Ledger Data", xlabel="Quarter", ylabel="Total (in USD million)", label="$ld_p Total by $yr_p for $rg_p") @df plot_data_a plot(:QTR, :TOTAL/10^8, title = "Finance Ledger Data", xlabel="Quarter", ylabel="Total (in USD million)", label=[ "Actuals by $yr_p for $rg_p $ldescr $adescr $ddescr" ], lw=3)ERROR: LoadError: UndefVarError: `@df` not defined in expression starting at REPL[44]:1julia> @df plot_data_b plot!(:QTR, :TOTAL/10^8, title = "Finance Ledger Data", xlabel="Quarter", ylabel="Total (in USD million)", label=[ "Budget by $yr_p for $rg_p $ldescr $adescr $ddescr" ], lw=3)ERROR: LoadError: UndefVarError: `@df` not defined in expression starting at REPL[45]:1