{"queries":[{"id":"unique_agencies","compiledQueryString":"SELECT DISTINCT agency_full_name\nFROM mta.forecast_accuracy_2023","inputQueryString":"SELECT DISTINCT agency_full_name\nFROM mta.forecast_accuracy_2023","compiled":false,"inline":true},{"id":"unique_expense_type","compiledQueryString":"SELECT DISTINCT expense_type\nFROM mta.forecast_accuracy_2023","inputQueryString":"SELECT DISTINCT expense_type\nFROM mta.forecast_accuracy_2023","compiled":false,"inline":true},{"id":"unique_items","compiledQueryString":"SELECT DISTINCT general_ledger\nFROM mta.forecast_accuracy_2023","inputQueryString":"SELECT DISTINCT general_ledger\nFROM mta.forecast_accuracy_2023","compiled":false,"inline":true},{"id":"forecast_info","compiledQueryString":"select * \nFROM mta.forecast_accuracy_2023\nwhere general_ledger = '${inputs.unique_items.value}' AND expense_type = '${inputs.unique_expense_type.value}' AND agency_full_name = '${inputs.unique_agencies.value}'","inputQueryString":"select * \nFROM mta.forecast_accuracy_2023\nwhere general_ledger = '${inputs.unique_items.value}' AND expense_type = '${inputs.unique_expense_type.value}' AND agency_full_name = '${inputs.unique_agencies.value}'","compiled":false,"inline":true},{"id":"unique_agencies","compiledQueryString":"SELECT DISTINCT agency_full_name\nFROM mta.forecast_accuracy_2023","inputQueryString":"SELECT DISTINCT agency_full_name\nFROM mta.forecast_accuracy_2023","compiled":false,"inline":true},{"id":"unique_expense_type","compiledQueryString":"SELECT DISTINCT expense_type\nFROM mta.forecast_accuracy_2023","inputQueryString":"SELECT DISTINCT expense_type\nFROM mta.forecast_accuracy_2023","compiled":false,"inline":true},{"id":"forecast_info_full","compiledQueryString":"SELECT \n agency_full_name,\n expense_type,\n\n -- Total amounts for Adopted Budget by Financial Plan Year\n SUM(total_adopted_budget_2019) AS total_adopted_budget_2019,\n SUM(total_adopted_budget_2020) AS total_adopted_budget_2020,\n SUM(total_adopted_budget_2021) AS total_adopted_budget_2021,\n SUM(total_adopted_budget_2022) AS total_adopted_budget_2022,\n\n -- Total amount for Actual 2023\n SUM(total_actual_2023) AS total_actual_2023,\n\n -- Differences between Actual and Adopted Budget for each year\n SUM(difference_2019_vs_actual) AS difference_2019_vs_actual,\n SUM(difference_2020_vs_actual) AS difference_2020_vs_actual,\n SUM(difference_2021_vs_actual) AS difference_2021_vs_actual,\n SUM(difference_2022_vs_actual) AS difference_2022_vs_actual,\n\n -- Percentage differences for each year\n AVG(CASE \n WHEN total_adopted_budget_2019 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2019) / total_adopted_budget_2019\n ELSE NULL \n END) AS percentage_diff_2019_vs_actual,\n\n AVG(CASE \n WHEN total_adopted_budget_2020 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2020) / total_adopted_budget_2020\n ELSE NULL \n END) AS percentage_diff_2020_vs_actual,\n\n AVG(CASE \n WHEN total_adopted_budget_2021 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2021) / total_adopted_budget_2021\n ELSE NULL \n END) AS percentage_diff_2021_vs_actual,\n\n AVG(CASE \n WHEN total_adopted_budget_2022 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2022) / total_adopted_budget_2022\n ELSE NULL \n END) AS percentage_diff_2022_vs_actual\n\nFROM forecast_accuracy_2023\nGROUP BY agency_full_name, expense_type","inputQueryString":"SELECT \n agency_full_name,\n expense_type,\n\n -- Total amounts for Adopted Budget by Financial Plan Year\n SUM(total_adopted_budget_2019) AS total_adopted_budget_2019,\n SUM(total_adopted_budget_2020) AS total_adopted_budget_2020,\n SUM(total_adopted_budget_2021) AS total_adopted_budget_2021,\n SUM(total_adopted_budget_2022) AS total_adopted_budget_2022,\n\n -- Total amount for Actual 2023\n SUM(total_actual_2023) AS total_actual_2023,\n\n -- Differences between Actual and Adopted Budget for each year\n SUM(difference_2019_vs_actual) AS difference_2019_vs_actual,\n SUM(difference_2020_vs_actual) AS difference_2020_vs_actual,\n SUM(difference_2021_vs_actual) AS difference_2021_vs_actual,\n SUM(difference_2022_vs_actual) AS difference_2022_vs_actual,\n\n -- Percentage differences for each year\n AVG(CASE \n WHEN total_adopted_budget_2019 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2019) / total_adopted_budget_2019\n ELSE NULL \n END) AS percentage_diff_2019_vs_actual,\n\n AVG(CASE \n WHEN total_adopted_budget_2020 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2020) / total_adopted_budget_2020\n ELSE NULL \n END) AS percentage_diff_2020_vs_actual,\n\n AVG(CASE \n WHEN total_adopted_budget_2021 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2021) / total_adopted_budget_2021\n ELSE NULL \n END) AS percentage_diff_2021_vs_actual,\n\n AVG(CASE \n WHEN total_adopted_budget_2022 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2022) / total_adopted_budget_2022\n ELSE NULL \n END) AS percentage_diff_2022_vs_actual\n\nFROM forecast_accuracy_2023\nGROUP BY agency_full_name, expense_type","compiled":false,"inline":true},{"id":"forecast_info_expense","compiledQueryString":"select * \nFROM (SELECT \n agency_full_name,\n expense_type,\n\n -- Total amounts for Adopted Budget by Financial Plan Year\n SUM(total_adopted_budget_2019) AS total_adopted_budget_2019,\n SUM(total_adopted_budget_2020) AS total_adopted_budget_2020,\n SUM(total_adopted_budget_2021) AS total_adopted_budget_2021,\n SUM(total_adopted_budget_2022) AS total_adopted_budget_2022,\n\n -- Total amount for Actual 2023\n SUM(total_actual_2023) AS total_actual_2023,\n\n -- Differences between Actual and Adopted Budget for each year\n SUM(difference_2019_vs_actual) AS difference_2019_vs_actual,\n SUM(difference_2020_vs_actual) AS difference_2020_vs_actual,\n SUM(difference_2021_vs_actual) AS difference_2021_vs_actual,\n SUM(difference_2022_vs_actual) AS difference_2022_vs_actual,\n\n -- Percentage differences for each year\n AVG(CASE \n WHEN total_adopted_budget_2019 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2019) / total_adopted_budget_2019\n ELSE NULL \n END) AS percentage_diff_2019_vs_actual,\n\n AVG(CASE \n WHEN total_adopted_budget_2020 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2020) / total_adopted_budget_2020\n ELSE NULL \n END) AS percentage_diff_2020_vs_actual,\n\n AVG(CASE \n WHEN total_adopted_budget_2021 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2021) / total_adopted_budget_2021\n ELSE NULL \n END) AS percentage_diff_2021_vs_actual,\n\n AVG(CASE \n WHEN total_adopted_budget_2022 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2022) / total_adopted_budget_2022\n ELSE NULL \n END) AS percentage_diff_2022_vs_actual\n\nFROM forecast_accuracy_2023\nGROUP BY agency_full_name, expense_type)\nwhere expense_type = '${inputs.unique_expense_type.value}' AND agency_full_name = '${inputs.unique_agencies.value}'","inputQueryString":"select * \nFROM ${forecast_info_full}\nwhere expense_type = '${inputs.unique_expense_type.value}' AND agency_full_name = '${inputs.unique_agencies.value}'","compiled":true,"inline":true},{"id":"unique_agencies","compiledQueryString":"SELECT DISTINCT agency_full_name\nFROM mta.forecast_accuracy_2023","inputQueryString":"SELECT DISTINCT agency_full_name\nFROM mta.forecast_accuracy_2023","compiled":false,"inline":true},{"id":"forecast_info_all","compiledQueryString":"SELECT \n agency_full_name,\n\n -- Total amounts for Adopted Budget by Financial Plan Year\n SUM(total_adopted_budget_2019) AS total_adopted_budget_2019,\n SUM(total_adopted_budget_2020) AS total_adopted_budget_2020,\n SUM(total_adopted_budget_2021) AS total_adopted_budget_2021,\n SUM(total_adopted_budget_2022) AS total_adopted_budget_2022,\n\n -- Total amount for Actual 2023\n SUM(total_actual_2023) AS total_actual_2023,\n\n -- Differences between Actual and Adopted Budget for each year\n SUM(difference_2019_vs_actual) AS difference_2019_vs_actual,\n SUM(difference_2020_vs_actual) AS difference_2020_vs_actual,\n SUM(difference_2021_vs_actual) AS difference_2021_vs_actual,\n SUM(difference_2022_vs_actual) AS difference_2022_vs_actual,\n\n -- Percentage differences for each year\n AVG(CASE \n WHEN total_adopted_budget_2019 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2019) / total_adopted_budget_2019\n ELSE NULL \n END) AS percentage_diff_2019_vs_actual,\n\n AVG(CASE \n WHEN total_adopted_budget_2020 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2020) / total_adopted_budget_2020\n ELSE NULL \n END) AS percentage_diff_2020_vs_actual,\n\n AVG(CASE \n WHEN total_adopted_budget_2021 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2021) / total_adopted_budget_2021\n ELSE NULL \n END) AS percentage_diff_2021_vs_actual,\n\n AVG(CASE \n WHEN total_adopted_budget_2022 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2022) / total_adopted_budget_2022\n ELSE NULL \n END) AS percentage_diff_2022_vs_actual\n\nFROM forecast_accuracy_2023\nGROUP BY agency_full_name","inputQueryString":"SELECT \n agency_full_name,\n\n -- Total amounts for Adopted Budget by Financial Plan Year\n SUM(total_adopted_budget_2019) AS total_adopted_budget_2019,\n SUM(total_adopted_budget_2020) AS total_adopted_budget_2020,\n SUM(total_adopted_budget_2021) AS total_adopted_budget_2021,\n SUM(total_adopted_budget_2022) AS total_adopted_budget_2022,\n\n -- Total amount for Actual 2023\n SUM(total_actual_2023) AS total_actual_2023,\n\n -- Differences between Actual and Adopted Budget for each year\n SUM(difference_2019_vs_actual) AS difference_2019_vs_actual,\n SUM(difference_2020_vs_actual) AS difference_2020_vs_actual,\n SUM(difference_2021_vs_actual) AS difference_2021_vs_actual,\n SUM(difference_2022_vs_actual) AS difference_2022_vs_actual,\n\n -- Percentage differences for each year\n AVG(CASE \n WHEN total_adopted_budget_2019 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2019) / total_adopted_budget_2019\n ELSE NULL \n END) AS percentage_diff_2019_vs_actual,\n\n AVG(CASE \n WHEN total_adopted_budget_2020 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2020) / total_adopted_budget_2020\n ELSE NULL \n END) AS percentage_diff_2020_vs_actual,\n\n AVG(CASE \n WHEN total_adopted_budget_2021 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2021) / total_adopted_budget_2021\n ELSE NULL \n END) AS percentage_diff_2021_vs_actual,\n\n AVG(CASE \n WHEN total_adopted_budget_2022 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2022) / total_adopted_budget_2022\n ELSE NULL \n END) AS percentage_diff_2022_vs_actual\n\nFROM forecast_accuracy_2023\nGROUP BY agency_full_name","compiled":false,"inline":true},{"id":"forecast_info_agency","compiledQueryString":"select * \nFROM (SELECT \n agency_full_name,\n\n -- Total amounts for Adopted Budget by Financial Plan Year\n SUM(total_adopted_budget_2019) AS total_adopted_budget_2019,\n SUM(total_adopted_budget_2020) AS total_adopted_budget_2020,\n SUM(total_adopted_budget_2021) AS total_adopted_budget_2021,\n SUM(total_adopted_budget_2022) AS total_adopted_budget_2022,\n\n -- Total amount for Actual 2023\n SUM(total_actual_2023) AS total_actual_2023,\n\n -- Differences between Actual and Adopted Budget for each year\n SUM(difference_2019_vs_actual) AS difference_2019_vs_actual,\n SUM(difference_2020_vs_actual) AS difference_2020_vs_actual,\n SUM(difference_2021_vs_actual) AS difference_2021_vs_actual,\n SUM(difference_2022_vs_actual) AS difference_2022_vs_actual,\n\n -- Percentage differences for each year\n AVG(CASE \n WHEN total_adopted_budget_2019 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2019) / total_adopted_budget_2019\n ELSE NULL \n END) AS percentage_diff_2019_vs_actual,\n\n AVG(CASE \n WHEN total_adopted_budget_2020 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2020) / total_adopted_budget_2020\n ELSE NULL \n END) AS percentage_diff_2020_vs_actual,\n\n AVG(CASE \n WHEN total_adopted_budget_2021 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2021) / total_adopted_budget_2021\n ELSE NULL \n END) AS percentage_diff_2021_vs_actual,\n\n AVG(CASE \n WHEN total_adopted_budget_2022 <> 0 \n THEN (total_actual_2023 - total_adopted_budget_2022) / total_adopted_budget_2022\n ELSE NULL \n END) AS percentage_diff_2022_vs_actual\n\nFROM forecast_accuracy_2023\nGROUP BY agency_full_name)\nwhere agency_full_name = '${inputs.unique_agencies.value}'","inputQueryString":"select * \nFROM ${forecast_info_all}\nwhere agency_full_name = '${inputs.unique_agencies.value}'","compiled":true,"inline":true}]}

Agency Forecasting Analysis

Choose an Agency

No Results