Jump to content
We've recently updated our Privacy Statement, available here. ×

use over function to find specific value in another column


Matthew Pickett 2

Recommended Posts

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

Link to comment
Share on other sites

  • 2 months later...

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...