Cypher year on year calculation

Neo4j Graph Platform
,
1

So I have following nodes with properties defined:

Nodes:
SPENDING

  • spending_id
  • amount

SPENDING_CATEGORY

  • category

YEAR

  • year

Relationship:
(SPENDING)-[SPEND_ON]->(SPENDING_CATEGORY)
(SPENDING)-[SPEND_IN]->(YEAR)

Right now I want to do a year on year comparsion to compare the percentage changes of spending in each category with 2022 vs 2023, expect the output would be:
category, 2022_spending, 2023_spending, percentage_changes

Anyone can suggest a cypher to achieve this?

2

Try this:

match(y:Year)<-[:SPEND_IN]-(s:Spending)-[:SPEND_ON]->(c:Category)
with y.year as year, c.name as category, sum(s.amount) as total
order by category, year
with category, collect({year: year, total: total}) as categoryStats
unwind range(size(categoryStats)-1, 0, -1) as index
return
    category, 
    categoryStats[index].year as year, 
    categoryStats[index].total as total,
    CASE WHEN index <> 0 THEN categoryStats[index-1].total ELSE "N/A" END as previousYearTotal,
    CASE WHEN index <> 0 THEN round(100 * (categoryStats[index].total - categoryStats[index-1].total) / categoryStats[index-1].total, 2) ELSE "N/A" END AS percentChangeFromPreviousYear

create 
(y2021:Year{year:2021}), 
(y2022:Year{year:2022}), 
(y2023:Year{year:2023}),
(cClothes:Category{name:"Clothes"}), 
(cElectronics:Category{name:"Electronics"}), 
(cSupplies:Category{name:"Supplies"}),
(s0:Spending{spending_id:0, amount:100.00}),
(s1:Spending{spending_id:1, amount:200.00}),
(s2:Spending{spending_id:2, amount:300.00}),
(s3:Spending{spending_id:3, amount:400.00}),
(s4:Spending{spending_id:4, amount:500.00}),
(s5:Spending{spending_id:5, amount:600.00}),
(s6:Spending{spending_id:6, amount:700.00}),
(s7:Spending{spending_id:7, amount:800.00}),
(s8:Spending{spending_id:8, amount:900.00}),
(s9:Spending{spending_id:9, amount:55.00}),
(s0)-[:SPEND_ON]->(cClothes), (s0)-[:SPEND_IN]->(y2021), 
(s1)-[:SPEND_ON]->(cElectronics), (s1)-[:SPEND_IN]->(y2021), 
(s2)-[:SPEND_ON]->(cSupplies), (s2)-[:SPEND_IN]->(y2021), 
(s3)-[:SPEND_ON]->(cElectronics), (s3)-[:SPEND_IN]->(y2022), 
(s4)-[:SPEND_ON]->(cClothes), (s4)-[:SPEND_IN]->(y2022), 
(s5)-[:SPEND_ON]->(cElectronics), (s5)-[:SPEND_IN]->(y2021), 
(s6)-[:SPEND_ON]->(cClothes), (s6)-[:SPEND_IN]->(y2023), 
(s7)-[:SPEND_ON]->(cElectronics), (s7)-[:SPEND_IN]->(y2021), 
(s8)-[:SPEND_ON]->(cSupplies), (s8)-[:SPEND_IN]->(y2022), 
(s9)-[:SPEND_ON]->(cElectronics), (s9)-[:SPEND_IN]->(y2023)

Screen Shot 2024-01-09 at 4.16.46 PM
Screen Shot 2024-01-09 at 4.16.46 PM2326×1050 186 KB

Screen Shot 2024-01-09 at 4.17.32 PM
Screen Shot 2024-01-09 at 4.17.32 PM1328×822 64.1 KB

Maybe this is a little cleaner version:

match(y:Year)<-[:SPEND_IN]-(s:Spending)-[:SPEND_ON]->(c:Category)
with y.year as year, c.name as category, sum(s.amount) as total
order by category, year
with category, collect({year: year, total: total}) as categoryStats
unwind range(size(categoryStats)-1, 0, -1) as index
with
    category, 
    categoryStats[index].year as year, 
    categoryStats[index].total as total,
    CASE WHEN index <> 0 THEN categoryStats[index-1].total ELSE "N/A" END as previousYearTotal
return
    category,
    year, 
    total,
    previousYearTotal,
    CASE WHEN previousYearTotal <> "N/A" THEN round(100 * (total - previousYearTotal) / previousYearTotal, 2) ELSE "N/A" END AS percentChangeFromPreviousYear
3

Thanks!!! That is working for me.

1 Like