Forum Discussion

Betsy's avatar
Betsy
Helper IV
5 years ago
Solved

Insert a subsequent index value from same column into calculated column

Hi,   Haven't asked a question here for a long while. I have academic data that I am looking to calculate enrollment from term to term. Each subsequent term will have a higher term number, but thes...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    This calculated column formula works for enrolled next term

    =CALCULATE(MIN(Data[enrolled]),FILTER(Data,Data[student]=EARLIER(Data[student])&&Data[start_date]>EARLIER(Data[start_date])&&Data[term]>EARLIER(Data[term])))

Betsy's avatar
Betsy
Helper IV
5 years ago

Hi Ashish,

 

Ashish_Mathur Here's the example file I posted above, with the expected result in the enrolled_next_term and enrolled_next_year columns.Google sheets file 

 

Thanks for looking at this!

 

Betsy

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

This calculated column formula works for enrolled next term

=CALCULATE(MIN(Data[enrolled]),FILTER(Data,Data[student]=EARLIER(Data[student])&&Data[start_date]>EARLIER(Data[start_date])&&Data[term]>EARLIER(Data[term])))

  • Betsy's avatar
    Betsy
    Helper IV
    5 years ago

    Hi Ashish,

     

    Ashish_Mathur Thanks so much. Works perfectly. I used your formula as an example and used index +1 to produce enrolled_next_year too:

     

    enrolled_next_year = CALCULATE(MIN(dummy_data[enrolled]),FILTER(dummy_data,dummy_data[student]=EARLIER(dummy_data[student])&&dummy_data[index]>EARLIER(dummy_data[index]) +1 &&dummy_data[term]>EARLIER(dummy_data[term])))
     
    Thanks again for taking your valuable time to help me. I really appreciate it!
     
    Betsy
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      5 years ago

      You are welcome.

      • Betsy's avatar
        Betsy
        Helper IV
        5 years ago

        Hi Ashish_Mathur 

         

        I'm having trouble with this formula when looking at real data. I made a dummy subset of this data. When I have 419 students, with 4 terms each worth of enrollment, I should see an enrolled next term count of 967, but instead the count shows 873, so 94 students are not being counted as enrolled (or 1). From what I can tell most of these (75) that are calculated as 0 rather than 1 are from enrolled_next_term for the first term (23/index 1) and the rest (19) are from the second term (44/index 2). The third term (79/index3) is counting correctly showing 301 students enrolled_next_term. 

         

        I'm not sure that MIN(dummy_data[enrolled]) might not be functioning as expected? We want to just look up the value of enrolled at the >earlier[start_date] or [term] (I don't think we need both) somehow?

         

        Thanks again! I cannot for the life of me figure out why this isn't working the same on the two sets of data.

         

        Betsy

         

        dummy_data2