NeoDash Line Chart with a line for each Year

[gq16]

Hello Graphers! (Specifically @niels_dejong may be able to help?)

I'm working in NeoDash trying to create an output similar to this one:

but I have not been able to get NeoDash to display multiple lines and a legend for each year. When I try, this is what I get:

the year appears as a straight line. It doesn't treat it as a separate parameter with which to create multiple lines and a legend on the line chart so that I can observe differences in trends in the line. Here is my Cypher:

MATCH (q:Quantity)<-[hq:HAS_QUANTITY]-(sh:Shipment)-[oo:ON_ORDER]->(s:Sales)-[fc:FOR_CONTRACTED]->(a:Account),(s)-[he:HAS_ENTRY]->(oe:OrderEntry)-[hl:HAS_LINE]->(ol:OrderLine)-[hpo:HAS_PRODUCT_ORIGIN]->(po:ProductOrigin)-[hp:HAS_PRODUCT]->(p:Product)-[hpt:HAS_PRODUCT_TYPE]->(pt:ProductType)

WHERE pt.name = $neodash_producttype_name AND a.name = $neodash_account_name 

RETURN sum(q.quantity) AS Quantity, sh.shippedDate.month AS Month, sh.shippedDate.year AS Year

ORDER BY Month

Appreciate everyone's help in advance! #NeoDash

[niels_dejong · Accepted solution]

Hi @gq16 , nice to see you are exploring NeoDash line charts some more.
What you want to do here is transform your data so that you have one output column for each of the (year, value) pairs to show in the chart. Here's an example with artificial data:

// Start off with some artificial data in the format that we would read from Neo4j.
UNWIND [
{year: 2020, month: 1, value: 3},
{year: 2020, month: 4, value: 4},
{year: 2020, month: 8, value: 2},
{year: 2020, month: 11, value: 3},
{year: 2021, month: 1, value: 5},
{year: 2021, month: 4, value: 6},
{year: 2021, month: 8, value: 5},
{year: 2021, month: 11, value: 7},
{year: 2022, month: 1, value: 10},
{year: 2022, month: 4, value: 9},
{year: 2022, month: 8, value: 9},
{year: 2022, month: 11, value: 11}
] as row

// Order it by year.
WITH row.year as year, row.month as month, row.value as value
ORDER BY year ASC 
// Then, collect the (year, value) pairs by month.
WITH month,  collect([year, value]) as value_year_pairs
// Now return each pair as a seperate column in the results.
RETURN month, 
       value_year_pairs[0][1] as value_2020, 
       value_year_pairs[1][1] as value_2021, 
       value_year_pairs[2][1] as value_2022

While this feels a little strange, it should work:

Could you try if this works for you?
Best,
Niels

[gq16]

@niels_dejong I appreciate your response, I tried implementing it in the method below, and it definitely has solved the problem of my being able to stack lines in the chart, but it seems that the values are summing all into one of the years, so I'm only getting one line with real data, while the the two years after it have no data. Why would this be? Would this be due to where the aggregation is occurring using the "WITH" clauses?

My goal is to have a data point on each month that has a value of the sum of the sales for that month, and having a line for each year, in order to compare sales trends.

Cypher: 

MATCH (q:Quantity)<-[hq:HAS_QUANTITY]-(sh:Shipment)-[oo:ON_ORDER]->(s:Sales)-[fc:FOR_CONTRACTED]->(a:Account),(s)-[he:HAS_ENTRY]->(oe:OrderEntry)-[hl:HAS_LINE]->(ol:OrderLine)-[hpo:HAS_PRODUCT_ORIGIN]->(po:ProductOrigin)-[hp:HAS_PRODUCT]->(p:Product)-[hpt:HAS_PRODUCT_TYPE]->(pt:ProductType)
WHERE a.name = $neodash_account_name AND sh.shippedDate.year > 2018 AND pt.name = $neodash_producttype_name
WITH sum(ol.quantity) AS Quantity, sh.shippedDate.year AS Year, sh.shippedDate.month AS Month
ORDER BY Year
WITH Month, Year,collect([Year,Quantity]) AS Quantity_Year_Pairs
RETURN Month,
Quantity_Year_Pairs[0][1] AS `2019` ,
Quantity_Year_Pairs[1][1] AS `2020` ,
Quantity_Year_Pairs[2][1] AS `2021`
ORDER BY Month

 Output:

As you can see, 2020 and 2021 show no data.

Thanks in advance,

GQ

[niels_dejong]

hi @gq16 , you are correct, there's one too many variable in your aggregation in the final with clause. If you change:

WITH Month, Year,collect([Year,Quantity]) AS Quantity_Year_Pairs

to

WITH Month, collect([Year,Quantity]) AS Quantity_Year_Pairs

 it should work. Could you give this a shot?

[gq16]

Yep, it looks great now!