hello guys, I just want to determine if the student got low grade or high grade every week
IIF([Name] = [Row+1:Name] AND [Grade.Rate]>[Row+1:Grade.Rate] , 'Grade went low', null())
Date | Name | Grade | Preferred result from the multi-row formula above |
2021-02-05 | Andre | 3 | Grade went low |
2021-01-22 | Andre | 5 | Grade went low |
2021-02-05 | Test | 3 | Grade went low |
2021-01-22 | Test | 5 | Grade went low |
2021-02-05 | Test5 | 3 | Grade went low |
2021-01-22 | Test5 | 6 | Grade went low |
2021-02-05 | Test6 | 6 | Grade went high |
2021-01-22 | Test6 | 3 | Grade went high |
Hi @Ultralightbeam ,
I would suggest using the Cross Tab tool,
check the attached solution and let me know if it works for you.
In the multirow tool - you don't need to check the name.
Instead, set the grouping in the tool; and set the "value for rows that don't exist"
Then you can write a formula like:
if isnull([Row-1: Grade.Rate]) //meaning this is the first time
then "First time"
elseif [Row-1: Grade.Rate] < [Grade.Rate]
then "Grade increased"
elseif [Row-1: Grade.Rate] > [Grade.Rate]
then "Grade decreased"
else
"Grade same or other condition"
endif