Table backfills data for empty values

[gq16]

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:

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

 

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

[ameyasoft]

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

[gq16]

 Seems to still be giving the same result. @ameyasoft 

[ameyasoft]

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

[gq16]

Still the same.😭 

[glilienfield]

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. 

[glilienfield]

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

[glilienfield]

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

[gq16 · Accepted solution]

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:

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

[gq16]

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.