Table backfills data for empty values

Here is my problem: I want to look at varying sales each day of the month for April, May, and June, but I don't have any sales for some days in April. Why does Neo4j Backfill the values for May into April and June into May if there is no value for April? Can someone help!

Here is the table I'm expecting:

gq16_0-1667578149400.png

Here is the table I'm getting (It's just the first few days but the issue is clear):

Neo4j Incorrect Table Drawing.png

Here is my query:

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 = 'Winfield Solutions LLC' AND pr.type = 'UREA' AND d.id.year = 2022 AND d.id.month IN [4,5,6]
WITH sum(cl.quantity32e) AS Quantity32e,d.id.month AS Month, d.id.day AS Day
ORDER BY Month
WITH Day, collect([Month,Quantity32e]) AS Quantity_Month_Pairs
RETURN Day,
	Quantity_Month_Pairs[0][1] AS `April`,
	Quantity_Month_Pairs[1][1] AS `May` ,
    Quantity_Month_Pairs[2][1] AS `June`
ORDER BY Day

I believe the root cause is that each month does not have data for every day. As such, when you are accessing the first index in your two-dimensional array, it is not always true that index 0 represents 'April', index 1 represents 'May', and index 2 represents 'June'. When the day is missing for a month, the later months shift down so the months that do have days always start at index 0. That explains the behavior you are seeing.

Try this:

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 = 'Winfield Solutions LLC' AND pr.type = 'UREA' AND d.id.year = 2022 AND d.id.month IN [4,5,6]
WITH d, COALESCE(cl.quantity32e, 0) as qty32e
WITH sum(qty32e) AS Quantity32e,d.id.month AS Month, d.id.day AS Day
ORDER BY Month
WITH Day, collect([Month,Quantity32e]) AS Quantity_Month_Pairs
RETURN Day,
	Quantity_Month_Pairs[0][1] AS `April`,
	Quantity_Month_Pairs[1][1] AS `May` ,
    Quantity_Month_Pairs[2][1] AS `June`
ORDER BY Day

Try this:

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 = 'Winfield Solutions LLC' AND pr.type = 'UREA' AND d.id.year = 2022 AND d.id.month IN [4,5,6]
WITH sum(cl.quantity32e) AS Quantity32e,d.id.month AS Month, d.id.day AS Day
ORDER BY Month
WITH Day, collect([Month,Quantity32e]) AS Quantity_Month_Pairs
RETURN Day,
	COALESCE([i in Quantity_Month_Pairs where i[0] = 'April' | i[1]][0], 0),
	COALESCE([i in Quantity_Month_Pairs where i[0] = 'May' | i[1]][0], 0),
	COALESCE([i in Quantity_Month_Pairs where i[0] = 'June' | i[1]][0], 0)
ORDER BY Day

Seems to still be giving the same result. @ameyasoft

gq16_0-1667594091280.png

If it does work, you should be able to remove the 'ORDER BY' on line 4.

Try this:

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 = 'Winfield Solutions LLC' AND pr.type = 'UREA' AND d.id.year = 2022 AND d.id.month IN [4,5,6]
WITH sum(cl.quantity32e) AS Quantity32e,d.id.month AS Month, d.id.day AS Day

WITH COALESCE(Quantity32e, 0) as Quantity32e, Month, Day ORDER BY Month

WITH Day, collect([Month,Quantity32e]) AS Quantity_Month_Pairs
RETURN Day,
	Quantity_Month_Pairs[0][1] AS `April`,
	Quantity_Month_Pairs[1][1] AS `May` ,
    Quantity_Month_Pairs[2][1] AS `June`
ORDER BY Day

Still the same.:sob:

gq16_0-1667596859317.png

Now it simply gives nothing but zeros. It seems to me that the inside of the coalesce statement is not finding any data, and therefore is coalescing all the values to 0.

gq16_0-1667921395912.png

I take it back! It works, you just made an error in the coalesce statement saying that i[0] = 'April' rather than saying i[0] = 4 since the months are saved in the Quantity_Month_Pairs as numerical values, not the month names.

Here is the modified cypher:

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 = 'Winfield Solutions LLC' AND pr.type = 'UREA' AND d.id.year = 2022 AND d.id.month IN [4,5,6]
WITH sum(cl.quantity32e) AS Quantity32e,d.id.month AS Month, d.id.day AS Day
ORDER BY Month
WITH Day, collect([Month,Quantity32e]) AS Quantity_Month_Pairs
RETURN Day,
	COALESCE([i in Quantity_Month_Pairs where i[0] = 4| i[1]][0], 0) AS April,
	COALESCE([i in Quantity_Month_Pairs where i[0] = 5| i[1]][0], 0) AS May,
	COALESCE([i in Quantity_Month_Pairs where i[0] = 6 | i[1]][0], 0) AS June
ORDER BY Day

And here are the awesome results:

gq16_0-1667923820764.png

Amazing teamwork! Thank you @glilienfield & @ameyasoft for your help!