Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Lets say I have data which includes the student name, week number & their weekly grades. This data is updated every week.
I want to create a chart which will only show the student whos grades dropped more than 30% from the past week.
for ex.
Student | Week | Grade |
Brian | 1 | 77 |
Brian | 2 | 67 |
Brian | 3 | 86 |
Brian | 4 | 68 |
Brian | 5 | 66 |
Brian | 6 | 83 |
Brian | 8 | 27 |
In this example, the students grade dropped significantly(over 30%) in week 8. We want to be able to see all students whos grades have dropped more than 30%, just like in this example.
How would I go about doing this?
One solution is:
tab1:
LOAD *, If(Diff>30, 'Red') As Status;
LOAD *, If(Student=Peek(Student), Peek(Grade)- Grade) As Diff;
LOAD * INLINE [
Student, Week , Grade
Brian, 1, 77
Brian, 2, 67
Brian, 3, 86
Brian, 4, 68
Brian, 5, 66
Brian, 6, 83
Brian, 8, 27
];
Output:
Another idea, if you want to do this just in a straight table without modifying your script, create the table with Student and Week and ordered by Student and Week. Then create a measure like:
=if((Above(Sum(Grade)) - Sum(Grade)) / Above(Sum(Grade)) >= .3, (Above(Sum(Grade)) - Sum(Grade)) / Above(Sum(Grade)), 0)
In the Add-ons --> data handling property of the table uncheck "Include zero values".