3 Replies Latest reply on Aug 1, 2017 8:16 AM by Ben Neville

Trying to obtain an overall aggregate score in comparing first/last observations for client data arranged in rows

Hi,

I've been racking my brain on this problem all day and have the distinct feeling that I'm overthinking it.  My data is arranged in rows by client and I would like to see if there has been an improvement in the ratio of Developmental Age to Chronological Age in comparing the first evaluation (Dev Snapshot ID) to the last evaluation.  I developed some simple LOD and filter criteria in order to look at group changes from first to last evaluation while weeding out the middle observations (Worksheet titled: Change in Developmental Age Scores - First/Last); however, I would also like to see the %age of children who improve from first to last observation, regardless of whether they Meet or Exceed their Developmental Age over time.

I initially developed a calculated field to recode the data (BetterSameWorse) when I realized that the organization of the data by row would make this impossible.  My first thought was to see if there was a way to convert the row data to column data, but I couldn't figure that out (fail), then I played with a few different LOD expressions (fail) and now I'm wondering if there is just a way to subtract the last Dev Age/Chron Age score from the first where a '0' result would be no change, a positive would be a positive and change and a negative result would constitute a negative change, and then just recode and aggregate the results accordingly.

Thoughts, help, guidance would be greatly appreciated.

Packaged workbook is attached.

Thank you!

Jason

• 1. Re: Trying to obtain an overall aggregate score in comparing first/last observations for client data arranged in rows

IF {FIXED [Participant ID]: MAX(IF [Dev Snapshot ID] = [FirstSnapshot ] THEN [Dev Age/Chron Age] END)}

< {FIXED [Participant ID]: MAX(IF [Dev Snapshot ID] = [LastSnapshot] THEN [Dev Age/Chron Age] END)}

THEN 1 ELSE 0 END

2 of 2 people found this helpful
• 2. Re: Trying to obtain an overall aggregate score in comparing first/last observations for client data arranged in rows

Hi Ben,

Thank you VERY MUCH for the time and suggestion.  This definitely got me on the right path.  This is where I landed and I seemed to work.  I just need to spend a little more time checking my results against the raw data.

Jason

//BETTER//

IF {FIXED [Participant ID]: MAX(IF [Dev Snapshot ID] = [FirstSnapshot ] THEN [Dev Age/Chron Age] END)}

< {FIXED [Participant ID]: MAX(IF [Dev Snapshot ID] = [LastSnapshot] THEN [Dev Age/Chron Age] END)} THEN 1

//SAME//

ELSEIF {FIXED [Participant ID]: MAX(IF [Dev Snapshot ID] = [FirstSnapshot ] THEN [Dev Age/Chron Age] END)}

= {FIXED [Participant ID]: MAX(IF [Dev Snapshot ID] = [LastSnapshot] THEN [Dev Age/Chron Age] END)} THEN 2

//WORSE//

ELSEIF {FIXED [Participant ID]: MAX(IF [Dev Snapshot ID] = [FirstSnapshot ] THEN [Dev Age/Chron Age] END)}

> {FIXED [Participant ID]: MAX(IF [Dev Snapshot ID] = [LastSnapshot] THEN [Dev Age/Chron Age] END)} THEN 3 ELSE 0 END

• 3. Re: Trying to obtain an overall aggregate score in comparing first/last observations for client data arranged in rows

You're correct - in my example you can SUM the calculation to get total participants improved, but in yours you could calculate the 3 groups and do cohort and other comparative analyses - it all depends on your use case.

2 of 2 people found this helpful