This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results forΒ

**Head's Up!** Site migration is underway.
Phase 2: migrate recent content

- Neo4j
- Technical Discussions
- Neo4j Graph Platform
- Is reduce() the right way to do Cumulative Sums in...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Options

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-01-2022 07:50 AM

If I wanted to perform a cumulative sum operation similar to that of:

```
int x = 0;
int i = 0;
for ( i = 0; i = 10, i++)
{
x = x +5
}
system.out.println(x);
```

where I would get a returned value of 55.

How would I perform this action in Neo4j using cypher if the value I am accumulating is a node property? Is the reduce() function the proper way to do this?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-15-2022 10:03 AM - edited β09-15-2022 10:11 AM

I think this is it. I show the result for the test data, where the data is the exact same for each month. You can see each years starts with the January total and the output is the same for each year, which is should since the sales are identical for the test data.

```
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
```

Adapting it to your query, this should work:

```
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
```

BTW- the query does not limit itself to only the years 2019-2021. Only the output is limited to those years due to selecting only those years in lines 10-12. If you data goes back further in years, the query is calculating those years but not using the totals. You can add a predicate to the 'where' clause on line 2 to to restrict the years to only those needed for the report.

You can also easily add or remove specific years for your report by adding/removing years on lines 10-12. For instance, to add 2022, just add a line for it as follows:

```
[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`,
[i in monthlyTotals where i.year = 2022 | i.sum ][0] as `2022`
```

20 REPLIES 20

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-01-2022 08:53 AM - edited β09-01-2022 08:55 AM

The 'reduce' works over a list of elements, so if you already have a list that is a good method. If you don't, then you can use the 'sum' function to sum the value over each record (or a subset if you have a grouping).

If you have a collection, you can also 'unwind' the collection into rows and use the 'sum' over the rows. The best approach depends on your scenario.

I provide examples using your scenario. The test data is generated from the following:

```
unwind range(0, 10) as i
create(n:Test{value:i})
```

With 'sum' function over rows of data:

```
match(n:Test)
return sum(n.value)
```

With 'reduce', after creating the list of values for the purpose of demonstration:

```
match(n:Test)
with collect(n.value) as values
return reduce(s = 0, i in values | s + i) as sum
```

Both return 55.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-01-2022 08:58 AM

@glilienfield I appreciate the response, the actual application of how I'm trying to use this is 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) 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)"*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-01-2022 10:15 AM

I think your error is a result of the following expression on line 5:

`total + x.quantity`

'x' is an element of 'Quantities', which is the collection of 'q.quantities', which is a double value. The expression 'x.quantity' is expecting 'x' to be a map, as you are using dot notation to access a member of the map. Since 'x' is a double value, you get the error message.

I gathered from reading your explanation and the code, you are trying to compute the cumulative sum of the monthly quantity shipped. I just refactored the code to the following. Sorry, I don't have any test data to try it on. It runs without error, but that doesn't mean it works.

```
MATCH (q:Quantity)<-[:HAS_QUANTITY]-(sh:Shipment)-[:ON_ORDER]->(s:Sales)-[:FOR_CONTRACTED]->(a:Account{name: $neodash_account_name})
WHERE sh.shippedDate.year >= 2019
AND 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 monthlyTotal
ORDER BY date({year: Year, month: Month}) ASC
WITH collect({year: Year, month: Month, quantity: monthlyTotal}) as monthlyStats
WITH monthlyStats, range(0, size(monthlyStats)-1) as indexes
UNWIND indexes as index
RETURN monthlyStats[index].month as Month, monthlyStats[index].year as Year, reduce(s=0, x in [i in indexes where i <= index | monthlyStats[i].quantity] | s + x) as cummulatedQuantity
```

Line 1 and 2 match on the account to get the quantity shipped, while restricting the shipping to the years greater and equal to 2019, as well as ensuring the each sales contains the specified product type. Question, it doesn't seem that the quantity being calculated is just for those products with the indicated type. Isn't the quantity the total quantity shipped for the entire sale?

Line 4 sums the quantity for each month and year, while line 5 orders the monthly totals by year and month. This is to order them for the accumulation calculation.

Line 6 collects each month into a map, so we can calculate the cumulative sums on the list.

Line 7 creates a list of integers representing each element in the list. This will be used to access each element by index.

Line 8 unwinds the indexes into a single integer (index) that represents how far we want to reduce the list of monthly stats by, i.e. one month, two months, etc.

Line 9 returns the month and year, along with the cumulative sum of the monthly status up and including that month.

As a note, this is not very efficient, as each cumulative sum starts from index 0 each time. I don't know of a way to it otherwise.

Let me know if this doesn't work and what the issue is. I can try to fix it. Or, if it is totally off, what I missed. Do you have any test data I can use?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-02-2022 10:33 AM

Firstly, thank you for the robust response, but it isn't working as expected. Here is the result:

What seems to be missing is the collection of each line for each year, since we see the sum restarts back at month 1 (Jan) once it reaches the end of the year. I'll work on solving this as well and share the solution if/when I figure it out.

To answer your question: "*Question, it doesn't seem that the quantity being calculated is just for those products with the indicated type. Isn't the quantity the total quantity shipped for the entire sale?"*

**The quantity being calculated in the tableau image I shared is from all products and all shipments regardless of party (for the sake of example), but what I am trying to make an output for just the indicated product for a specific party. **

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-02-2022 11:25 AM

It looks like the graph is by month number and does not consider the year. This seem problematic for data that spans multiple years. I would think the graph would start again at the origin when a year boundary is crossed, since the year component is being truncated.

I don't think your query will give you that, since the product type is eventually related to a sale, while the quantity is related to a sale. I don't see a relationship between the product and how much quantity was included on the sale. The quantity in your query seems to represent the total quantity shipped for the sale.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-02-2022 11:36 AM

**a single product type.** One sale cannot have multiple products.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-07-2022 03:54 PM

@glilienfield @niels_dejong I've gotten really close to figuring it out! I've been able to get the lines to split accordingly.

**It looks like there is just an issue now of the count not starting back at zero (for 2019 and 2021 lines, see green arrow in attached image) and that the sum goes down at random towards the end of the chart for the 2020 line (see red drawn line for expected behavior).**

Here is my cypher, let me me know if you have any advice!

```
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 monthlyTotal
ORDER BY date({year: Year, month: Month}) ASC
WITH collect({year: Year, month: Month, quantity: monthlyTotal}) as monthlyStats
WITH monthlyStats, range(0, size(monthlyStats)-1) as indexes
UNWIND indexes as index
WITH monthlyStats[index].month as Month, collect([monthlyStats[index].year, reduce(s=0, x in [i in indexes where i <= index | monthlyStats[i].quantity] | s + x)]) as cummulatedQuantity
RETURN Month,
cummulatedQuantity[0][1] AS `2019` ,
cummulatedQuantity[1][1] AS `2020` ,
cummulatedQuantity[2][1] AS `2021`
ORDER BY Month
```

Here is the result I'm getting:

I appreciate any feedback!

GQ16

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-09-2022 01:34 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-09-2022 03:11 PM

Correct. **I want the annual sum to reset to zero at the end of the previous year, but I want the yearly values to start at whatever the total sales for month 1 (January) are, so they wouldn't technically start at zero.** I hope that makes sense. Essentially, the point at January, should be the quantity of sales in the month of January, so I would expect it to start above zero. So in reality, the light green line for 2019, is correct (ignore the green arrow I had drawn pointing to zero) but I want the orange line for 2020 to NOT start where the previous year left off. I want this chart to compare the *TREND *in our sales. which is why I'd like to overlay them.

Also, in this data set there is no sales data after October 2020 which is why there shouldn't be any increase, but I would expect that the red line would continue horizontally until the year is over, but that isn't a big deal if we can't get that figured out.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-09-2022 04:34 PM

I got it. My mistake, the origin is January, so it should represent Januaryβs sales. I will look into it

extending line into the future doesnβt seem right. Itβs not showing anything is not showing the trend. The line should probably just stop at the last month.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-12-2022 04:53 PM

Give this a try:

```
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 collect({year: year, month: month, quantity: quantity}) as monthlyStats
WITH monthlyStats, range(0, size(monthlyStats)-1) as indexes
UNWIND indexes as index
WITH monthlyStats[index].month as month, monthlyStats[index].year as year, reduce(s=0, i in [x in indexes where x <= index | monthlyStats[x].quantity] | s + i) as sum
WITH month, collect({year: year, sum: sum}) 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
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-13-2022 07:03 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-13-2022 10:47 AM - edited β09-13-2022 10:49 AM

I see the error in my ways. The 'year' property of a date is returned as a number. I was testing as a string. Try this:

```
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 collect({year: year, month: month, quantity: quantity}) as monthlyStats
WITH monthlyStats, range(0, size(monthlyStats)-1) as indexes
UNWIND indexes as index
WITH monthlyStats[index].month as month, monthlyStats[index].year as year, reduce(s=0, i in [x in indexes where x <= index | monthlyStats[x].quantity] | s + i) as sum
WITH month, collect({year: year, sum: sum}) 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
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-13-2022 10:53 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-13-2022 11:01 AM - edited β09-13-2022 11:02 AM

What year is the green line that has not shipments? Is that correct? It looks like it is starting the new year were it left off, plus the January shipments. Assume the green line is 2019, which ends with zero shipments. I assume the blue line is year 2020, which does start above where 2019 left off. Year 2022, which I assume is red, starts off above where the 2020 line ends. where the difference should be the January 2022 sales. No?

Something is wrong with the blue line, as all lines should be non-decreasing. Can I get the shipment counts by year and month?

```
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}))
RETURN sh.shippedDate.month AS month, sh.shippedDate.year AS year, sum(q.quantity) AS quantity
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-13-2022 02:46 PM - edited β09-13-2022 02:51 PM

I went ahead and replaced your query results with some fixed data over the three years to observe the computed results. Each shipment has a quantity of 10 to keep it easy, but the number of shipments per month varies. The accumulated sum month-over-month is correct, with the years continuing as desired.

```
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 collect({year: year, month: month, quantity: quantity}) as monthlyStats
WITH monthlyStats, range(0, size(monthlyStats)-1) as indexes
UNWIND indexes as index
WITH monthlyStats[index].month as month, monthlyStats[index].year as year, reduce(s=0, i in [x in indexes where x <= index | monthlyStats[x].quantity] | s + i) as sum
WITH month, collect({year: year, sum: sum}) 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
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-15-2022 08:53 AM

This is great, but I DO want the lines to restart at the value for the January Sales for THAT year. So if 2019 sales ended at 230, and January 2020 sales were , I would want the 2020 line to start at the y value 40, not 270 (in this example). So instead of the 2020 line going from 270 to 460, I would want it to go from 40 to 230. This would make the lines overlap in a way such that I can compare the sales trends from one year to the next. That way I can achieve an output similar to that of the first tableau report I shared with you. Hopefully the image with the drawing below shows you what I mean in terms of where the 2020 and 2021 lines should be.

I'm just stumped at how to create 3 different sums in a way that achieves the desired outcome but is also scalable.

Thank you so much for your time and devotion to this problem! @glilienfield

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-15-2022 09:00 AM

I got it now. You want three independent lines that sum just that year. Ok, I will fix it

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-15-2022 10:03 AM - edited β09-15-2022 10:11 AM

I think this is it. I show the result for the test data, where the data is the exact same for each month. You can see each years starts with the January total and the output is the same for each year, which is should since the sales are identical for the test data.

```
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
```

Adapting it to your query, this should work:

```
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
```

BTW- the query does not limit itself to only the years 2019-2021. Only the output is limited to those years due to selecting only those years in lines 10-12. If you data goes back further in years, the query is calculating those years but not using the totals. You can add a predicate to the 'where' clause on line 2 to to restrict the years to only those needed for the report.

You can also easily add or remove specific years for your report by adding/removing years on lines 10-12. For instance, to add 2022, just add a line for it as follows:

```
[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`,
[i in monthlyTotals where i.year = 2022 | i.sum ][0] as `2022`
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

β09-15-2022 10:24 AM

You @glilienfield , are a hero.

I will study this query to understand it's exact nuance and how you got everything working. I also really appreciate the comment on being able to add multiple years. That is awesome. Thank you for staying with me through this!

Nodes 2022

All the sessions of the conference are now available online

Related Content

- Exclude some labels from nodes list of a path in Neo4j Graph Platform
- Running forever when using APOC with transitive closures and combining the paths in Neo4j Graph Platform
- Match query with relationship is taking too long to retrieve results does it mean we need to upgrade in Neo4j Graph Platform
- Reduce a graph in Neo4j Graph Platform
- Getting "The transaction has been closed." error while projecting a subgraph with GDS in Neo4j Graph Platform