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])))

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 

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        Hi,

        It is very difficult to identify the problem this way.  Please tell me in which specific row(s) of my calculated column formula is the result not correct i.e. is it 0 when in fact it should be 1.