Alasql Date Formatting and Plotting Cumulative Data

:wave: Hey,

Just wondering if anyone has been able to plot cumulative data easily using the chart component or a QUERY JSON WITH SQL component

I have various data stored in MongoDB - but an example is users

My MongoDB query gives me my active user data by created date e.g.
created date, user_id
2021-12-02T03:43:55.118Z, id1
2021-12-03T03:43:55.118Z, id2
2021-12-03T04:43:55.118Z, id3
2021-12-04T03:43:55.118Z, id4

I can create charts of total users but i'd like to have 2 charts that show:

  1. Users created by Day (blocked here as alasql doesn't recognise moment as a function)
  2. Cumulative user growth (blocked here as I can't work out how to create cumulative data in my query)

What I would like to plot is:
created_at, total_users

Hi @vicdavies01,

It sounds like you'll be needing 2 different data sets here:

  1. Users created by Day - Like you mentioned you should be able to use Query JSON with SQL in combination with your initial request to your API for fetching your data. You'll be needing to use the SQL WHERE statement here to specify users created on your specific date.

You should also be able to make the date part of your SQL WHERE statement dynamic too using double-curlies/transformer (e.g. {{dateInput.value}})

  1. Cumulative user growth - You should be able to take the data you received from the *Users created by Day *data, and if it's an array, take the length of it to determine the number of total_users. Make sure to arrange your data for your chart similarly to the one we have in our docs for Grouping data in Plotly (an array of objects).

For example:

  { "date": "2021-12-02, "total_users": {{usersCreatedByDay_2021_12_02.value}} } 

Hope this helps!