Forum Discussion

Betsy's avatar
Betsy
Icon for Helper IV rankHelper 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
Icon for Helper IV rankHelper 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
    Icon for Super User rankSuper 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
      Icon for Helper IV rankHelper 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
        Icon for Super User rankSuper User
        5 years ago

        You are welcome.