NeoDash Grouped Bar Chart Query Structure

Neo4j Graph Platform Cypher
,
1

I want to create a bar chart like this where I can see multiple quantities stacked for a given Product:

image

In my NeoDash Bar Chart Query, I'm returning this (all the quantity values are properties of a single node (f:Forecast) and I'm applying some filters earlier in the query):

RETURN  pr.name AS Product, sum(f.contracted_qty) AS Contracted, sum(f.total_opportunities_qty) ,
sum(f.won_opportunities_qty) AS Won, sum(f.lost_opportunities_qty) AS Lost,
sum(f.open_opportunities_qty) AS, sum(f.shipped_qty) AS Shipped

I've been able to create grouped bar charts before in NeoDash, but the issue is that in this case, due to all the quantities being different properties within a single node, NeoDash can only display one value at a time for the given Product and I cannot group them into a stacked bar chart to get the view I'm hoping for.

image
image1920×997 49.9 KB

Any advice on how I can aggregate/group the values so I can get the stacked bar chart like the above ?

2

@bennu_neo, @niels_dejong , @glilienfield
Do you guys have any advice for this? I really want to create what was visualized above in the stacked bar chart from Tableau. The best workaround I've been able to achieve has been using the radar charts since they can visualize many properties of the same index, but it is a bit hard to look at and really understand if the data isn't close in value. I really would just like the radar chart logic to work in the bar chart.

Query Structure:
MATCH (p:Party { name: $neodash_party_name})-[:ASSIGNED_TO]->(f:Forecast)<-[:IS_ASSIGNED_TO]-(pr:Product), (f)-[:FORECASTED_FOR]->(i:Industry), (f)-[:HAS_MODE]->(m:Mode), (f)-[:SHIPS_TO]->(st:StateOrProvince) WHERE pr.type = CASE WHEN isEmpty($neodash_product_type) THEN pr.type ELSE $neodash_product_type END RETURN pr.type, SUM(f.total_opportunities_qty) as totalOppQty, ROUND(SUM(f.won_opportunities_qty), 2) as wonOppQty, ROUND(SUM(f.lost_opportunities_qty), 2) as lostOppQty, ROUND(SUM(f.open_opportunities_qty), 2) as openOppQty, ROUND(SUM(f.shipped_qty), 2) as shippedQty

Radar Chart Result:

image
image752×867 62.5 KB

3

I have not used NeoDash, but I just went a reviewed on of their example dashboards that has a stacked bar graph. From what I gather, you need three values: 1) category (this is what is shown on the x-axis), 2) a group (different segments of the staged bar graph), and 3) a value (represents the height of the segment). Once you have these items returned from your query, you need to select a 'Bar Chart' for the 'Type' and set the following under 'advanced-settings': 'Grouping' On, and 2) 'Group Mode' as 'Stacked'.

It looks like your category is 'Product'. Do you want the group to have values: 'Contracted', 'Won', 'Lost', 'Shipped', and the other two missing, and the values of each corresponding to the 'sums'? This would mean the segments of each stacked bar chart would be the group values. Is this what you want?

4

You are correct. The true goal is to have just 4 values, Won, Lost, and Open Opportunity Quantities, and then the Shipped Quantities all summed with relation to a product.
Here is the tricky part:
Shipped Qty is a subset of the Won Quantity (how much of the opportunities that we "Won" have been shipped to the customer), so really, I wouldn't want it "grouped" with the three "Opportunity" Quantities. I want to essentially see some version of these two mockups from other platforms.
In this one, the black horizontal line represents the shipped qty:
image
In this one the second bar represents the shipped qty:
image

This is my latest attempt, but I'm still doesn't work:

MATCH (p:Party)-[:ASSIGNED_TO]->(f:Forecast{current_flg:'Y'})<-[:IS_ASSIGNED_TO]-(pr:Product),
(f)-[:SHIPS_FROM]->(city:City),
(f)-[:SHIPS_FROM]->(state:StateOrProvince),
(f)-[:SHIPS_FROM]->(coun:Country),
(f)-[:SHIPS_TO]->(city2:City),
(f)-[:SHIPS_TO]->(state2:StateOrProvince),
(f)-[:SHIPS_TO]->(coun2:Country),
//(f)<-[:CREATED]->(marketer:ContractOwner),
//(f)-[:TITLE_TRANSFER_AT]->(t:TitleTransfer),
//(f)-[:FORECASTED_FOR]->(i:Industry),
(f)-[:HAS_MODE]->(m:Mode)

WHERE p.name = $neodash_party_name 
AND city.id = CASE WHEN isEmpty($neodash_city_id) THEN city.id ELSE $neodash_city_id END
AND f.forecast_month >= CASE WHEN isEmpty(toString($neodash_forecast_forecast_month_1)) THEN f.forecast_month ELSE $neodash_forecast_forecast_month_1 END
AND f.forecast_month <= CASE WHEN isEmpty(toString($neodash_forecast_forecast_month_2)) THEN f.forecast_month ELSE $neodash_forecast_forecast_month_2 END
//AND pr.type = CASE WHEN isEmpty($neodash_product_type) THEN pr.type ELSE $neodash_product_type END

WITH pr.type AS productType, 
round(sum(f.won_opportunities_qty),2) AS won_qty,
round(sum(f.lost_opportunities_qty),2) AS lost_qty,
round(sum(f.open_opportunities_qty),2) AS open_qty,
round(sum(f.shipped_qty),2) AS shipped_qty
WITH productType, {Won:won_qty,Lost:lost_qty,Open:open_qty,Shipped:shipped_qty} AS typeQtyMap
//WITH productType, collect([productType,won_qty]) AS won,
//collect([productType,lost_qty]) AS lost,
//collect([productType,open_qty]) AS open,
//collect([productType,shipped_qty]) AS shipped

RETURN productType, typeQtyMap, keys(typeQtyMap) AS keys
5

Hi @gq16 @glilienfield ,

Perhaps you can use a transformation in the Cypher query to shape the data into the format the bar chart expects. Something like this?

CREATE (a:Company{name: "Apple", revenue: 14, profit: 3, loss: 4})
CREATE (b:Company{name: "Banana", revenue: 17, profit: 2, loss: 6})

Then to visualize it:

MATCH (c:Company)
UNWIND ['revenue', 'profit', 'loss'] as group
WITH *
RETURN c.name, group, c[group] as value

image
image1650×890 60.9 KB

Would something like this work?

Best regards,
Niels

1 Like
6

Yes, that was my thought. Just wanted see what he wanted before modifying his code.

7

Following @niels_dejong suggestion, you can modify your query to the following. BTW- some of the matches from 'f' to other entities are not used, so they should be removed for efficiency purposes.

MATCH (p:Party)-[:ASSIGNED_TO]->(f:Forecast{current_flg:'Y'})<-[:IS_ASSIGNED_TO]-(pr:Product),
(f)-[:SHIPS_FROM]->(city:City),
(f)-[:SHIPS_FROM]->(state:StateOrProvince),
(f)-[:SHIPS_FROM]->(coun:Country),
(f)-[:SHIPS_TO]->(city2:City),
(f)-[:SHIPS_TO]->(state2:StateOrProvince),
(f)-[:SHIPS_TO]->(coun2:Country),
(f)-[:HAS_MODE]->(m:Mode)

WHERE p.name = $neodash_party_name 
AND city.id = CASE WHEN isEmpty($neodash_city_id) THEN city.id ELSE $neodash_city_id END
AND f.forecast_month >= CASE WHEN isEmpty(toString($neodash_forecast_forecast_month_1)) THEN f.forecast_month ELSE $neodash_forecast_forecast_month_1 END
AND f.forecast_month <= CASE WHEN isEmpty(toString($neodash_forecast_forecast_month_2)) THEN f.forecast_month ELSE $neodash_forecast_forecast_month_2 END

WITH pr.type AS productType, 
round(sum(f.won_opportunities_qty),2) AS won_qty,
round(sum(f.lost_opportunities_qty),2) AS lost_qty,
round(sum(f.open_opportunities_qty),2) AS open_qty,
round(sum(f.shipped_qty),2) AS shipped_qty
WITH productType, {Won:won_qty,Lost:lost_qty,Open:open_qty,Shipped:shipped_qty} AS typeQtyMap

UNWIND keys(typeQtyMap) as key
RETURN productType as category, key as group, typeQtyMap[key] as value
1 Like
8

@glilienfield and @niels_dejong you guys are heroes. Thanks for your help! I definitely will use this logic if I'm in a similar situation going forward.

image
image1511×867 12 KB