cancel
Showing results for 
Search instead for 
Did you mean: 

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

converting list to constant

Avi
Node Clone

without success I tried to convert a list to a constant (please see attached)
I created the correct calculation 

MATCH p=(n:Account{Account:'Acct 6100'})-[r:Driver]->(d:Department)
return  apoc.coll.sum(collect(r.Driver)) as driverSum
and retrieve total of 196 , but when I tried to use it for calculation to dived each number by the total with 
MATCH p=(n:Account{Account:'Acct 6100'})-[r:Driver]->(d:Department)
return  d.Department as name,r.Driver as driver,apoc.coll.sum(collect(r.Driver)) as driverSum,n.Amount as alloc_amt
it retrieve the same r.Driver
 
the question is there a way to convert a list to a constant (196) or we have to use collect and the function for each?
 
 
1 ACCEPTED SOLUTION

glilienfield
Ninja
Ninja

I am not sure what you are looking for, but here is an example of calculating the driver total by department and then dividing each by the total across all departments. 

match (n:Account{Account:'Acct 6100'})-[r:Driver]->(d:Department)
with n, d.Department as dept, sum(r.Driver) as deptDriverTotal
with n, collect({dept: dept, total: deptDriverTotal}) as deptStats, sum(deptDriverTotal) as total
unwind deptStats as deptStat
return n.amount as amt, deptStat.dept as department, deptStat.total as deptTotal, 100 * (toFloat(deptStat.total) / toFloat(total)) as deptPercentage

What are you trying to accomplish with your last query? In summary, it looks like you have calculated the total of all the Drivers and then updated the driver relationship with the ratio of the driver's Driver value and the total. As a note, there is no need for the merges. There is an alternative approach. 

View solution in original post

5 REPLIES 5

glilienfield
Ninja
Ninja

As a note, you can calculate the sum directly with ‘sum(r.Driver)’ instead of apoc.coll.sum(collect(r.Driver)). 

what is it you want to calculate? Do you want the total, or the total by driver, and/or ratio of driver total divided by total for each driver? 

Thank you for taking the time , I would like to create a division of each  driver  by the total

Avi
Node Clone

I successfully generated the allocation with the query below.
is there a simple way to achieve the same results without collect and index

 

MATCH p=(n:Account{Account:'Acct 6100'})-[r:Driver]->(d:Department)
with  sum(r.Driver) as sum,collect(d.Department) as dep,collect(r.Driver) as driver,n.Amount as amount
unwind range(0,size(dep)-1) as index
merge(e:Department{Department:dep[index]})
merge(p:Account{Account:'Acct 6100'})
merge(p)-[r:Driver]->(e)
SET r.allocation=(toFloat(r.Driver)/sum)*amount
return r.allocation,r.Department

 

glilienfield
Ninja
Ninja

I am not sure what you are looking for, but here is an example of calculating the driver total by department and then dividing each by the total across all departments. 

match (n:Account{Account:'Acct 6100'})-[r:Driver]->(d:Department)
with n, d.Department as dept, sum(r.Driver) as deptDriverTotal
with n, collect({dept: dept, total: deptDriverTotal}) as deptStats, sum(deptDriverTotal) as total
unwind deptStats as deptStat
return n.amount as amt, deptStat.dept as department, deptStat.total as deptTotal, 100 * (toFloat(deptStat.total) / toFloat(total)) as deptPercentage

What are you trying to accomplish with your last query? In summary, it looks like you have calculated the total of all the Drivers and then updated the driver relationship with the ratio of the driver's Driver value and the total. As a note, there is no need for the merges. There is an alternative approach. 

Thank you!