Forum Discussion

MClipsham92's avatar
MClipsham92
Frequent Visitor
7 years ago
Solved

Specifying a Row using two columns

Hi All,

 

Apologies if the title wasn't very clear I was having trouble thinking of how to word it.

 

I have some survey data from students from multiple points in a programme. Each survey the students complete they are rated from 1-5 in a category. I'm trying to write a conditional column that will be either "Improved", "Stayed the Same" or "Not Improved" for each category. I had this working as an IF statement but have recently unpivoted the data in order to get line graphs to work and now can't figure out how to write this.

 

The unpivoted data looks like this:

 

 

There is also a column called Name.1 that I've not included because it contains individual student names.

 

My previous column was a series of nested IF statements that basically went IF (Resilience Start of Programme > Resilience Week 12), "Did not Improve",IF Etc Etc

 

Any help would be great.

 

Thanks,

 

 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    7 years ago

    MClipsham92 

     

    Hope I understand your requirement clearly. Thanks.

     

    Column = 
VAR a= if(Sheet3[attribute]="listening end of week 12",MAXX(FILTER(Sheet3,Sheet3[student]=EARLIER(Sheet3[student])&&Sheet3[attribute]="resilience start of programme"),Sheet3[score]))
return if(ISBLANK(a),BLANK(),if(Sheet3[score]<a,"Did not improve","Improved"))

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    7 years ago

    MClipsham92 

     

    Hope I understand your requirement clearly. Thanks.

     

    Column = 
VAR a= if(Sheet3[attribute]="listening end of week 12",MAXX(FILTER(Sheet3,Sheet3[student]=EARLIER(Sheet3[student])&&Sheet3[attribute]="resilience start of programme"),Sheet3[score]))
return if(ISBLANK(a),BLANK(),if(Sheet3[score]<a,"Did not improve","Improved"))

    • MClipsham92's avatar
      MClipsham92
      Frequent Visitor
      7 years ago

      I've no idea how I'd go about writing that myself but it seems to have worked with a tiny bit of tweaking, I've ended up with this: 

       

      Listening Improved Term 1 = VAR a= IF(Instructor[Attribute]="listening End of Week 12",MAXX(FILTER(Instructor,Instructor[Name.1]=EARLIER(Instructor[Name.1])&&Instructor[Attribute]="Listening Start of Programme"),Instructor[Score]))
return if(ISBLANK(a),BLANK(),if(Instructor[Score]<a,"Did not improve","Improved"))
      This works well but how would I go about adding in a "Stayed the same" result if the score are the same? (currently counting as "Improved". Is it a case of editing the if statement at the end? 
       
      Also an explanation of how it works so I could to learn to write similar myself in the future would be amazing, or maybe a link to an article discussing it? 
       
      Thanks!