Help with Chart Group By Count

Wappler General How To
#1

I have a dmx bar chart that I need to base on a server connect API (as returned below)
I cannot get it to group by activity_type and then count each in the chart datasource

{
  "apiActivityCount": {
    "status": 200,
    "headers": {
      "content-type": "application/json;charset=UTF-8",
      "access-control-allow-origin": "*",
      "date": "Thu, 03 Sep 2020 08:54:19 GMT",
      "connection": "close"
    },
    "data": {
      "$totalResults": 4,
      "$resources": [
        {
          "$key": 16754,
          "$title": "",
          "activity_Id": 16754,
          "activity_type": "Email"
        },
        {
          "$key": 16755,
          "$title": "",
          "activity_Id": 16755,
          "activity_type": "Holiday"
        },
        {
          "$key": 16750,
          "$title": "",
          "activity_Id": 16750,
          "activity_type": "Email"
        },
        {
          "$key": 16756,
          "$title": "",
          "activity_Id": 16756,
          "activity_type": "Email"
        },
        {
          "$key": 16757,
          "$title": "",
          "activity_Id": 16757,
          "activity_type": "Meeting"
        }
      ]
    }
  }
}

Help would be really appreciated.

Converting API data to be displayed on charts
#2

Appreciate Thursdays are a busy day for the Wappler team, but could do with getting some help on this. I had read a few others having queries about the Group By on charts.

Is there anything I can do post Server Connect API to format the results to play nicely with the X / Y axis?

#3

If someone could just confirm that they can group by activity_type in the above json data and count in a chart that would be really appreciated.

I have spent hours using all sorts of combinations within the UI and not getting the desired results.

#4

@patrick @George @Teodor please help.
Ideally I need to get this working today.

#5

Hello, which part are you struggling with exactly?
Why not group them in the server action and return the activity type and the count?

#6

Hi @Teodor
How do I group using the Server Connect API Action?
I cannot see the option to Group By like I do in the chart datasource.

#7

From what I understand is that you want to show in the chart the number of Email, Holiday and Meeting.

First create a variable component, here we first group the required data by activity type.

<dmx-value id="chartData" dmx-bind:value="api1.data.apiActivityCount.data.$resources.groupBy('activity_type')"></dmx-value>

In the chart for Data Source use chartData.value.keys() and for Axis Labels use $value.
Data Set 1, Label Activity and in Values use chartData.value[$value].length.

Chart then looks like:
image

1 Like
#8

Add a set value step and use the group by formatter there for the resources. Group by activity_type and enable the output option for the set value step.

Then use the set value step as a data source and $key as a label:

Screenshot_2

Use $value.count() as values for the data set:

Screenshot_3

EDIT: sorry it seems i posted the same time as Patrick did :slight_smile:

#9

Thank you both for your help.
I was struggling to achieve this within the Wappler UI

#10

@Teodor, @patrick - thank you for your feedback to mimuk - charts are something i’ve struggled with.

How would you display chart data over time?

E.g. I have a table that is capturing page views, and the date the page view was created.

I’d like to be able to show the number of page views by day, week or month - but can’t seem to achieve this in the formatter.

#11

Hi @patrick
Sorry to bring this up again.
I got the original chart working perfectly following your assistance.
However, I need to create another chart based on the same server connect data with another field included.

    {
      "$key": 16750,
      "$title": "",
      "activity_Id": 16750,
      "activity_type": "Email",
      "activity_cost": "150.00"
    },
    {
      "$key": 16751,
      "$title": "",
      "activity_Id": 16751,
      "activity_type": "Meeting",
      "activity_cost": "250.00"
    },
    {
      "$key": 16752,
      "$title": "",
      "activity_Id": 16752,
      "activity_type": "Meeting",
      "activity_cost": "200.00"
    },

In the chart for Data Source use chartData.value.keys() and for Axis Labels use $value .
Data Set 1, Label Activity and in Values use chartData.value[$value].length .

The variable is still being grouped by ‘activity_type’
and the labels need to be the same, but I need to sum the activity_cost rather than count the number of items that are under a specific key.

Any help would be really appreciated

#12

Is there any way to use the Wappler Charts UI to group by / sum when the source is a server connect API connection?

#13

try chartData.value[$value].sum('activity_cost') in the values.