ojs-dashboard – SWINNO

dbfile = FileAttachment("SWINNO.UDIT.sqlite3").sqlite()
// dbfile = SQLiteDatabaseClient.open(FileAttachment("https://zenodo.org/api/records/10602309/files/SWINNO.UDIT.sqlite3/content").url())

innovation = dbfile.sql`select id, name_sv, prod_code, year from innovation`

viewof year1 = Inputs.number(
  [1970, 2020],
  {value: 1970, step: 1, label: "YoC1"}
)

viewof year2 = Inputs.number(
  [1970, 2020],
  {value: 2020, step: 1, label: "YoC2"}
)

viewof sector = Inputs.toggle({label: "Codes", value: true})


innovations = dbfile.query(`select id, name_sv, prod_code, year from innovation where ${year1} <= year and ${year2} >= year`)
trend = sector ? dbfile.query(`select year, count(*) as count, "p"||substr(prod_code, 0, 3) as prod_code from innovation where ${year1} <= year and ${year2} >= year group by year, prod_code`) : dbfile.query(`select year, count(*) as count from innovation where ${year1} <= year and ${year2} >= year group by year`)

ninns = dbfile.query(`select count(*) as c from innovation where ${year1} <= year and ${year2} >= year`)

html`<div align="center">Innovations<br> <font size="7">${ninns[0]['c']}</font><div>`

narts = dbfile.query(`select count(*) as c  from article where ${year1} <= year and ${year2} >= year`)

html`<div align="center">Articles<br> <font size="7">${narts[0]['c']}</font><div>`

nents = dbfile.query(`select  count(distinct entity_id) as c from innovation_entity as ie join innovation as i on ie.innovation_id == i.id where ${year1} <= year and ${year2} >= year`)

html`<div align="center">Entities<br> <font size="7">${nents[0]['c']}</font><div>`

Plot.plot({
  marks:
  sector ? [
    Plot.rectY(trend, Plot.binX({y: "count"})  ,{x:"year",  fill: "prod_code"}),
    // Plot.dot(trend, {x:"year", y:"count", fill: "prod_code", tip: true})
    Plot.ruleY[0]
  ] : [
Plot.line(
  trend,
  {
  x: "year",
  y: "count",
  stroke: "black",
  tip: true
})
],
y: {grid: true, zero: true, tip: true }
})

Inputs.table(trend)

More visualisations coming soon, data is availabie here