31:T1772,vega_embed({ '$schema': 'https://vega.github.io/schema/vega-lite/v2.6.0.json', 'padding': {'left': -60}, 'config': { 'view': { 'height': 300, 'width': 400 } }, 'data': { 'values': top_chart_data }, 'resolve': { 'scale': { 'x': 'shared', 'color': 'independent' } }, 'vconcat': [{ 'layer': [{ 'encoding': { 'color': { 'value': 'green' }, 'x': { 'axis': { 'labels': true, 'title': '' }, 'field': 'yearquarter_date_end', 'type': 'temporal' }, 'y': { 'axis': { 'title': 'Number of cases at end of quarter' }, 'field': 'sum_outstanding', 'type': 'quantitative' } }, 'height': 200, 'mark': 'line', 'title': 'Cases outstanding', 'width': 600 }, { 'encoding': { 'color': { 'value': 'green' }, 'opacity': { 'condition': { 'selection': 'selector004', 'value': 1 }, 'value': 0 }, 'text': { 'field': 'sum_outstanding', 'type': 'quantitative', 'format': ',.0f' }, 'x': { 'field': 'yearquarter_date_end', 'type': 'temporal' }, 'y': { 'field': 'sum_outstanding', 'type': 'quantitative' } }, 'height': 200, 'mark': { 'dx': 0, 'dy': -7, 'type': 'text' }, 'selection': { 'selector004': { 'empty': 'none', 'fields': [ 'yearquarter' ], 'nearest': true, 'on': 'mouseover', 'type': 'single' } }, 'title': 'Cases outstanding', 'width': 600 }] }, { 'layer': [{ 'encoding': { 'color': { 'value': '#b74f4f' }, 'x': { 'axis': { 'title': '' }, 'field': 'yearquarter_date_mid', 'type': 'temporal' }, 'y': { 'axis': { 'title': 'Number of cases per quarter' }, 'field': 'sum_receipts', 'type': 'quantitative' } }, 'height': 200, 'mark': 'line', 'title': 'Receipts and disposals', 'width': 600 }, { 'encoding': { 'color': { 'value': '#6b8dda' }, 'x': { 'axis': { 'title': '' }, 'field': 'yearquarter_date_mid', 'type': 'temporal' }, 'y': { 'field': 'sum_disposals', 'type': 'quantitative' } }, 'height': 200, 'mark': 'line', 'title': 'Receipts and disposals', 'width': 600 }, { 'encoding': { 'color': { 'field': 'legend_value', 'scale': { 'domain': [ 'Disposals', 'Receipts' ], 'range': [ '#6b8dda', '#b74f4f' ] }, 'type': 'nominal', 'legend': { 'title': '' } }, 'opacity': { 'condition': { 'selection': 'selector004', 'value': 1 }, 'value': 0 }, 'text': { 'field': 'chart_text', 'type': 'ordinal' }, 'x': { 'axis': { 'title': '' }, 'field': 'yearquarter_date_mid', 'type': 'temporal' }, 'y': { 'field': 'chart_text_y', 'type': 'quantitative' } }, 'mark': { 'dy': -10, 'type': 'text' }, 'width': 600 }, { 'encoding': { 'text': { 'field': 'receipts_label', 'type': 'ordinal' }, 'x': { 'axis': { 'title': '' }, 'field': 'yearquarter_date_mid', 'type': 'temporal' }, 'y': { 'field': 'sum_receipts', 'type': 'quantitative' }, 'color': { 'value': '#b74f4f' } }, 'mark': { 'dx': 5, 'type': 'text', 'align': 'left', 'baseline': 'middle' }, 'width': 600 }, { 'encoding': { 'text': { 'field': 'disposals_label', 'type': 'ordinal' }, 'x': { 'axis': { 'title': '' }, 'field': 'yearquarter_date_mid', 'type': 'temporal' }, 'y': { 'field': 'sum_disposals', 'type': 'quantitative' }, 'color': { 'value': '#6b8dda' } }, 'mark': { 'dx': 5, 'type': 'text', 'align': 'left', 'baseline': 'middle' }, 'width': 600 }, { 'encoding': { 'opacity': { 'condition': { 'selection': 'selector004', 'value': 1 }, 'value': 0 }, 'x': { 'field': 'yearquarter_date_mid', 'type': 'temporal' }, 'y': { 'field': 'sum_receipts', 'type': 'quantitative' }, 'y2': { 'field': 'sum_disposals', 'type': 'quantitative' } }, 'height': 200, 'mark': 'rule', 'selection': { 'selector004': { 'empty': 'none', 'fields': [ 'yearquarter' ], 'nearest': true, 'on': 'mouseover', 'type': 'single' } }, 'title': 'Cases outstanding', 'width': 600 }] }] })32:T4fe,vega_embed( { '$schema': 'https://vega.github.io/schema/vega-lite/v2.4.3.json', 'data': { 'values': db_2.exec('select * from df_current') }, 'layer': [{ 'encoding': { 'x': { 'field': 'yearquarter_date_end', 'type': 'temporal' }, 'y': { 'aggregate': 'sum', 'field': 'outstanding', 'type': 'quantitative' }, 'color': { 'value': 'green' } }, 'mark': {'type':'line', 'point' : 'true'}, 'title': `Oustanding cases` }, { 'mark': {'type': 'text', 'dy' : -10, 'align':'center', 'baseline':'middle'}, 'encoding': { 'x': { 'field': 'yearquarter_date_end', 'type': 'temporal' }, 'y': { 'aggregate': 'sum', 'field': 'outstanding', 'type': 'quantitative' }, 'text': { 'aggregate': 'sum', 'field': 'outstanding', 'type': 'quantitative', 'format': ',.0f', }, 'color': {'value': '#AAA'} } } ], 'width': 600 })33:T5cf,top_chart_data = { let numfmt = d3.format(',.0f') let df_summarised = db_2.exec(` select sum(disposals) as sum_disposals, sum(receipts) as sum_receipts, sum(outstanding) as sum_outstanding, sum(receipts) - sum(disposals) as receipts_less_disposals, sum(disposals) - sum(receipts) as disposals_less_receipts, yearquarter, yearquarter_date_mid, yearquarter_date_end from df_current group by yearquarter, yearquarter_date_mid, yearquarter_date_end`) df_summarised = _.map(df_summarised, function(d) { if (d.receipts_less_disposals > 0) { d.chart_text = `${d.yearquarter} :Receipts exceeded disposals by ${numfmt(d.receipts_less_disposals)}` d.chart_text_y = d.sum_receipts d.legend_value = 'Receipts' } else if (d.disposals_less_receipts > 0) { d.chart_text = `${d.yearquarter} :Disposals exceeded receipts by ${numfmt(d.disposals_less_receipts)}` d.chart_text_y = d.sum_disposals d.legend_value = 'Disposals' } else { d.chart_text = `Receipts equalled disposals` d.chart_text_y = d.sum_receipts d.legend_value = 'Receipts' } d.receipts_label = '' d.disposals_label = '' return d }) let last_value = df_summarised[df_summarised.length - 1] last_value.receipts_label = 'Receipts' last_value.disposals_label = 'Disposals' return df_summarised }34:[] 2f:{"title":"Better organised, more performant stats","cells":[{"id":0,"value":"md`# Better organised, more performant stats`","pinned":false,"hidden":false,"mode":"ojs"},{"id":118,"value":"report_period_html = html`Report for `","pinned":false,"hidden":false,"mode":"ojs"},{"id":334,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":231,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":245,"value":"import {htmlTable} from \"@rohscx/inputs\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":247,"value":"htmlTable(db_2.exec('select * from df_current').slice(0,10))","pinned":false,"hidden":false,"mode":"ojs"},{"id":207,"value":"selected_value = select_box_with_other_stuff(report_period_html)","pinned":false,"hidden":false,"mode":"ojs"},{"id":29,"value":"md`### Data import and filter`","pinned":false,"hidden":false,"mode":"ojs"},{"id":18,"value":"df_raw = {\n let url = \"https://gist.githubusercontent.com/RobinL/5a3fb37c43c017a051ea7ece1804d18b/raw/bb0816955fbd30514d993ab7c2cb379837abc41a/tidy_timeliness.csv\" \n \n let parse_row = function(d) {\n d.receipts = +d.receipts\n d.outstanding = +d.outstanding\n d.disposals = +d.disposals\n // Additional derivations\n d.yearquarter_date_mid = odm.timeparse_quarter_mid(d.yearquarter)\n d.yearquarter_date_end = odm.timeparse_quarter_end(d.yearquarter)\n return d\n }\n\n return d3.csv(url, parse_row)\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":33,"value":"db = {\n let db = new alasql.Database();\n db.exec('CREATE TABLE df_raw');\n db.exec('CREATE TABLE df_current');\n \n db.tables.df_raw.data = df_raw;\n return db\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":237,"value":"db_2 = {\n let df_current = db.exec(`select * from df_raw where yearquarter <= \"${report_period}\"`)\n \n\n \n db.tables.df_current.data = df_current;\n return db\n\n \n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":457,"value":"report_period = select_box_with_other_stuff(report_period_html)","pinned":false,"hidden":false,"mode":"ojs"},{"id":313,"value":"$33","pinned":false,"hidden":false,"mode":"ojs"},{"id":20,"value":"md`### Imports`","pinned":false,"hidden":false,"mode":"ojs"},{"id":24,"value":"d3 = require('d3')","pinned":false,"hidden":false,"mode":"ojs"},{"id":37,"value":"_ = require('lodash')","pinned":false,"hidden":false,"mode":"ojs"},{"id":47,"value":"alasql = require('alasql')","pinned":false,"hidden":false,"mode":"ojs"},{"id":2,"value":"odm = require(\"https://cdn.rawgit.com/RobinL/open_data_munge/1b10b9efd5a1eff0494fe6fc1f7b97a65167d8e3/build/open-data-munge.js\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":125,"value":"import {select_box_with_other_stuff} from \"@robinl/input-helpers\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":233,"value":"vega_embed = require(\"vega-embed@3\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":228,"value":"select2 = {}// $ = {\n// let $ = await require('jquery')\n// let s2 = await require('select2')\n// $('#tube-stations').select2({width:1000})\n// return $\n// }\n\n\n// md`\n// \n// here's a select box \n\n// needs space below\n\n// needs space below\n\n// needs space below\n\n// `","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W34","stdlib":"1","titling":"auto"}