use over function to find specific value in another column

[Matthew Pickett 2]

Hi there!

I've run into an issue creating a calculated column that i was hoping you could help me with. Essentially, i have the data below and i want to generate the last two columns using a calculated column. The column "sum $ of current project" can be calculated by using the over function sum($ content) over (Current Project).

However, i can't figure out how to calculate the last column - "sum $ of past project". This will essentially need to look at the current project column and use the value in the past project column to find the sum of $ content. In the example below the desired value will be $20.

Any help would be greatly appreciated.

 

 

 

Current Project

past project

item

$ content

sum $ current project

sum $ past project

 

 

project1

projectx

a

0.05

0.45

20

 

 

project1

projectx

b

0.07

0.45

20

 

 

project1

projectx

c

0.09

0.45

20

 

 

project1

projectx

d

0.11

0.45

20

 

 

project1

projectx

e

0.13

0.45

20

 

 

projectx

 

x

1

20

 

 

 

projectx

 

y

2

20

 

 

 

projectx

 

z

3

20

 

 

 

projectx

 

aa

4

20

 

 

 

projectx

 

b

5

20

 

 

 

projectx

 

v

5

20

[Khushboo Rabadia]

You can achieve this using few calculated columns:

sum of current Content project (CP) = Sum([$ content]) OVER ([Current Project])Then create a string list which will contain combination of Current Project name and sum of current Content project

Projectlist = UniqueConcatenate(Concatenate([Current Project]," - ",[CP]))Then create a calculated column for Past Project content sum

case when Find(",",Mid([projectlist],Find([past project],[projectlist]) + Len([past project]) + 3,Len([projectlist])))=0 then Mid([projectlist],Find([past project],[projectlist]) + Len([past project]) + 3,Len([projectlist])) else Mid([projectlist],Find([past project],[projectlist]) + Len([past project]) + 3,Find(",",Mid([projectlist],Find([past project],[projectlist]) + Len([past project]) + 3,Len([projectlist]))) - 1) endwhere Find([past project],[projectlist]) will fetch the position of past project in the projectlist + 3 (as " - " is added between project and content sum)

Mid([projectlist],Find([past project],[projectlist]) + Len([past project]) + 3,Len([projectlist])) = This will extract the rest of string starting from past project content

[Matthew Pickett 2]

This works perfectly! I appreciate it:)