[FIXED] How to turn the absolute numbers in a Stacked Bar Chart to relative numbers (in Neo4J Chart)?

Issue

This code

MATCH q = (r:Representative)-[f:FACILITATED]->(b)
WHERE NOT b:VuurpijlMatch
RETURN DISTINCT f.year AS index,
    b.type AS key,
    count(*) AS value
ORDER BY index ASC

gives me this graph in return. Since Neo4J's Chart shows a poor format, I therefore added manually the numbers at the X-as. I circled the absolute numbers as an example in the first bar.

Graph with absolute numbers

What I am looking for is a way to change the output from absolute numbers to relative numbers. See next graph as example of what I mean.

Desired output

Anyone who can help me plotting the relative values, thanks a lot!

Solution

To calculate relative values, you need the total count per year, along with the counts per label type. To do this we will need two aggregation ops, one to calculate the total count per year and another one to get the count per label per year. Then we can calculate the relative values, after filtering out the unnecessary rows. Something like this:

MATCH q = (r:Representative)-[f:FACILITATED]->(b)
WHERE NOT b:VuurpijlMatch
WITH DISTINCT f.year AS index, count(*) AS totalCountPerYear
MATCH q = (r:Representative)-[f:FACILITATED]->(b)
WHERE NOT b:VuurpijlMatch
WITH DISTINCT f.year AS year, index, totalCountPerYear, b.type AS key, count(*) AS typeValue
WITH index, key, round((typeValue * 100.0 / totalCountPerYear)) AS value WHERE index = year
RETURN index, key, value
ORDER BY index ASC

Answered By - Charchit Kapoor

Answer Checked By - Pedro (PHPFixing Volunteer)