Show Member Fluctuations per Year from join and leave dates

Hi

What am I trying to achieve:
I would like to have a time series chart showing the total number of members in my club at any time. This member count should be calculated by using the field “joining-date” and “leaving-date”. I’m thinking of it as a running sum - every filled field with a “joining-date” means +1 on the member count, every “leaving-date” entry is a -1.

I think, the main thing I am missing is: How can I assign a value (+1) to a populated field (joining-date)

Data structure
I’m calling the API of webling.ch with a secret key. This is my data structure with sample data per member:

[
  {
    "type": "member",
    "meta": {
      "created": "2020-03-02 11:33:00",
      "createuser": {
        "label": "Joana Doe",
        "type": "user"
      },
      "lastmodified": "2022-12-06 16:32:56",
      "lastmodifieduser": {
        "label": "Joana Doe",
        "type": "user"
      }
    },
    "readonly": true,
    "properties": {
      "Mitglieder ID": 99,
      "Anrede": "Dear",
      "Vorname": "Jon",
      "Name": "Doe",
      "Strasse": "Doeington Street",
      "Adresszusatz": null,
      "PLZ": "9999",
      "Ort": "Doetown",
      "E-Mail": "jon.doe@doenet.net",
      "Telefon Privat": null,
      "Telefon Geschäft": null,
      "Mobile": "099 877 54 54",
      "Geschlecht": "m",
      "Geburtstag": "1966-03-10",
      "Mitgliedschaftstyp": "Aktivmitgliedschaft",
      "Eintrittsdatum": "2020-03-01",
      "Austrittsdatum": null,
      "Passfoto": null,
      "Wordpress Benutzername": null,
      "Wohnhaft im Glarnerland": false,
      "Lat": "43.1563379",
      "Long": "6.0474622"
    },
    "parents": [
      240
    ],
    "children": {
    },
    "links": {
      "debitor": [
        2124,
        3056,
        3897
      ],
      "attendee": [
        2576
      ]
    },
    "id": 1815
  }
]

** data source **
I am using the “JSON API” by Marcus Olsson: GitHub - grafana/grafana-json-datasource: A data source plugin for loading JSON APIs into Grafana.

** basic query **
By calling the “/member?format=full” URL i can extract my two dates into two fields:

  • $.*.properties.[“Eintrittsdatum”] - Translates to “Joining date” in English
  • $.*.properties.[“Austrittsdatum”] - Translates to “Leaving date” in English

This means: a club member that has joined will only have it’s joining date filled, but a member that has joined and later left the club will have both fields filled.

Grafana v9.3.1 (89b365f8b1) on Linux