Jump to content

Calculating value from multiple rows with multiple delimiting variables

Ryan ODonnell1705507824

Recommended Posts

Looking to calculate a % difference (lift) between two different rows, which I believe I can do easily enough with an OVER function. My problem arises when trying to apply this to the appropriate rows, taking into account some additional criteria. In the attached picture I've laid out a simplified version of what I'm trying to accomplish, with colF being the desired calculated column to be created with the highlighted values.I need to make sure the % difference is calculated for every Net Population, for the specific Version, Granularity, and Project Code variables. I'm sure there is a fairly straightforward way of doing this (maybe the Previous, or Intersect functions). Any advice or feedback would be greatly appreciated. Thank you!
Link to comment
Share on other sites

figured this out, ended up essentially created two helpiercolumns and then applied an over - intersect - previous


created two helper fuctions


rank-test is simply control=1, net=2, treatment=3

identifier-row is concat of the identifying variables i want to calculate across



case when [population]="Treatment" then null

when [Final Rate] / First([Final Rate]) over (Intersect([identifier-row],Previous([rank-test]))) end

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...