NeoDash Cumulative Sum Line Chart

[gq16]

Hey Neo peeps!

I'm trying to create a shipment pace output that is cumulative (meaning the values add to the previous sum and the line chart should never go down) I'm attempting to do this by using the reduce() function (List functions - Neo4j Cypher Manual). It doesn't seem to be working as expected for me though.

This is one of my attempts at the Cypher (lines 3 & 5 particularly):

MATCH path =(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 >= 2019 AND pt.name = $neodash_producttype_name
WITH collect(q.quantity) AS Quantities, sh.shippedDate.year AS Year, sh.shippedDate.month AS Month
ORDER BY Year ASC //reduce(total = 0, x IN Quantities | total + x.quantity)
WITH Month, collect([Year,reduce(total = 0, x IN Quantities | total + x.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

Here is what I'm going for (note how all the lines only increase in value over time since it is a cumulative sum):

But I get the following error and I can't really find a workaround:

"Type mismatch: expected a map but was Double(1.099750e+02)"

I'd appreciate any advice on the dashboard output or on the reduce() function in general that could help me solve this problem. Thanks in advance, and  @niels_dejong 's help would be greatly appreciated!

GQ

[bennu_neo]

Hi @gq16 ,

Your error is on line 5 x.quantity  due to the fact that the property was already collected. Try with just x.

MATCH path =(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 >= 2019 AND pt.name = $neodash_producttype_name
WITH collect(q.quantity) AS Quantities, sh.shippedDate.year AS Year, sh.shippedDate.month AS Month
ORDER BY Year ASC //reduce(total = 0, x IN Quantities | total + x.quantity)
WITH Month, collect([Year,reduce(total = 0, x IN Quantities | total + x)]) 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

[glilienfield]

This problem seems very similar to the one we solved a few months back. Below was the solution then. Can you adapt it? 

with [[date('2019-01-01'),10],[date('2019-01-01'),10],[date('2019-02-01'),10],[date('2019-02-01'),10],[date('2019-02-01'),10],[date('2019-03-01'),10],[date('2019-03-01'),10],[date('2019-03-01'),10],[date('2019-04-01'),10],[date('2019-05-01'),10],[date('2019-06-01'),10],[date('2019-06-01'),10],[date('2019-07-01'),10],[date('2019-07-01'),10],[date('2019-08-01'),10],[date('2019-08-01'),10],[date('2019-09-01'),10],[date('2019-10-01'),10],[date('2019-10-01'),10],[date('2019-11-01'),10],[date('2019-11-01'),10],[date('2019-11-01'),10],[date('2019-12-01'),10],
[date('2020-01-01'),10],[date('2020-01-01'),10],[date('2020-02-01'),10],[date('2020-02-01'),10],[date('2020-02-01'),10],[date('2020-03-01'),10],[date('2020-03-01'),10],[date('2020-03-01'),10],[date('2020-04-01'),10],[date('2020-05-01'),10],[date('2020-06-01'),10],[date('2020-06-01'),10],[date('2020-07-01'),10],[date('2020-07-01'),10],[date('2020-08-01'),10],[date('2020-08-01'),10],[date('2020-09-01'),10],[date('2020-10-01'),10],[date('2020-10-01'),10],[date('2020-11-01'),10],[date('2020-11-01'),10],[date('2020-11-01'),10],[date('2020-12-01'),10],
[date('2021-01-01'),10],[date('2021-01-01'),10],[date('2021-02-01'),10],[date('2021-02-01'),10],[date('2021-02-01'),10],[date('2021-03-01'),10],[date('2021-03-01'),10],[date('2021-03-01'),10],[date('2021-04-01'),10],[date('2021-05-01'),10],[date('2021-06-01'),10],[date('2021-06-01'),10],[date('2021-07-01'),10],[date('2021-07-01'),10],[date('2021-08-01'),10],[date('2021-08-01'),10],[date('2021-09-01'),10],[date('2021-10-01'),10],[date('2021-10-01'),10],[date('2021-11-01'),10],[date('2021-11-01'),10],[date('2021-11-01'),10],[date('2021-12-01'),10]] as monthlyStats
unwind monthlyStats as stat
WITH stat[0].month AS month, stat[0].year AS year, sum(stat[1]) AS quantity
ORDER BY date({year: year, month: month}) ASC
WITH year, collect({month: month, quantity: quantity}) as monthlyStats
UNWIND range(1, 12) as month
WITH year, month, reduce(s=0, x in [i in monthlyStats where i.month <= month | i.quantity] | s + x) as monthTotal
WITH month, collect({year: year, sum: monthTotal}) as monthlyTotals
RETURN month as Month, 
[i in monthlyTotals where i.year = 2019 | i.sum ][0] as `2019`, 
[i in monthlyTotals where i.year = 2020 | i.sum ][0] as `2020`,
[i in monthlyTotals where i.year = 2021 | i.sum ][0] as `2021`
ORDER BY Month

[glilienfield]

Oops, that was my test code.  This is what the solution was adapted to your domain.  

MATCH (q:Quantity)<-[:HAS_QUANTITY]-(sh:Shipment)-[:ON_ORDER]->(s:Sales)-[:FOR_CONTRACTED]->(a:Account{name: $neodash_account_name})
WHERE EXISTS((s)-[:HAS_ENTRY]->(:OrderEntry)-[:HAS_LINE]->(:OrderLine)-[:HAS_PRODUCT_ORIGIN]->(:ProductOrigin)-[:HAS_PRODUCT]->(:Product)-[:HAS_PRODUCT_TYPE]->(:ProductType{name: $neodash_producttype_name}))
WITH sh.shippedDate.month AS month, sh.shippedDate.year AS year, sum(q.quantity) AS quantity
ORDER BY date({year: year, month: month}) ASC
WITH year, collect({month: month, quantity: quantity}) as monthlyStats
UNWIND range(1, 12) as month
WITH year, month, reduce(s=0, x in [i in monthlyStats where i.month <= month | i.quantity] | s + x) as monthTotal
WITH month, collect({year: year, sum: monthTotal}) as monthlyTotals
RETURN month as Month, 
[i in monthlyTotals where i.year = 2019 | i.sum ][0] as `2019`, 
[i in monthlyTotals where i.year = 2020 | i.sum ][0] as `2020`,
[i in monthlyTotals where i.year = 2021 | i.sum ][0] as `2021`
ORDER BY Month

[glilienfield]

Looking back at it, I don’t think the order by in line 4 is necessary, since the way the values are being extracted in line 7 doesn’t requiring an ordering. 

[gq16 · Accepted solution]

The thing is, we've refactored our model a bit, and also, I never did any serious data validation on the above query result. I believe that it may have also had some imperfections in its results, although it was a big improvement from the results I was getting before. I also currently only have data for 3 months loaded in this new graph model, so I'm trying to look at comparing months vs. comparing years as I was previously. But I believe I adapted it properly, let me know what you think!

MATCH (p:Party)-[:AGREED_TO]->(c:Contract)-[:HAS_LINE]->(cl:ContractLine)<-[:IS_ASSIGNED_TO]-(pr:Product),(cl)<-[:ACTUALIZED_ON]-(s:Shipment)-[:SHIPPED_ON]->(d:Date)
WHERE p.name = $neodash_party_name AND pr.type = $neodash_product_type AND d.id.year = 2022 AND d.id.month IN [4,5,6]
WITH sum(cl.quantity32e) AS quantity32e,d.id.year AS year, d.id.month AS month, d.id.day AS day
ORDER BY date({year:year, month: month, day: day}) ASC
WITH month, collect({day:day, quantity32e: quantity32e}) as dailyStats
UNWIND range(1, 31) as day
WITH month, day, reduce(s=0, x in [i in dailyStats where i.day <= day | i.quantity32e] | s + x) as dayTotal
WITH day, collect({month: month, sum: dayTotal}) as dailyTotals
RETURN day, 
[i in dailyTotals where i.month = 4 | i.sum ][0] as `April`, 
[i in dailyTotals where i.month = 5 | i.sum ][0] as `May`,
[i in dailyTotals where i.month = 6 | i.sum ][0] as `June`
ORDER BY day

 Check out my results, I think they are correct, but let me know if you see any blatant errors. 

Note:
I still have the issue with the other chart, the Shipment Trends Chart, this Shipment pace chart doesn't have the same issues because this one is a "summing" chart, so if there are no sales on a given day, the sum just adds 0, but the issue with the other one is that it is backfilling the values when there are no sales. That is the purpose for this question:
Table backfills data for empty values - Neo4j - 61741

[glilienfield]

It looks good.

I think the sorting on line 4 is not necessary, as the way of calculation the running totals for a day on line 7 doesn’t require the data to be ordered. The sort is an artifact of my original approach, which did need it, but was later approached differently once I understood your requirements.