Forum Discussion

ivandelgra's avatar
ivandelgra
Helper I
6 years ago

How to count instance changes in a column YoY

Hello I have this dataset:

 

ID PERSONCARD TYPESUBSCRIPTION YEAR
AFREE2018
APREMIUM2019
BPREMIUM2018
BPRO2019
CFREE2018
CPRO2019
DFREE2018
DPRO2018
EFREE2018
EPRO2018

 

I would like to know for each year (from 2018 to 2019):

  • how many FREE subscribers have changed their plan to PRO subscribers
  • how many FREE subscribers have not changed their plan
  • how many FREE subscribers have changed their plan to PREMIUM subscribers

I need of course the other combinations, too. 

Please note that someone can change the plan during the same year (as for D and E id person)
Can you help me?
 

 

Thank you so much. 

Greg_Deckler amitchandak Mariusz 

CountIfs

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    You can't do this because within a single year there's no way of telling the sequence of events in time.

    Unless there's a rule that says it's always true that if there are different card types for a person in a year, FREE always precedes PRO. But what will happen if somebody changes the type many times a year?

    Best
    D
    • ivandelgra's avatar
      ivandelgra
      Helper I
      6 years ago
      Hi Anonymous. Supposing instead that no one can change the plan during the same year, which solution you would find? Thank you
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        That's trivial then.

         

        I would like to know for each year (from 2018 to 2019):

        • how many FREE subscribers have changed their plan to PRO subscribers
          • 0
        • how many FREE subscribers have not changed their plan
          • just count the people with FREE entries in the table
        • how many FREE subscribers have changed their plan to PREMIUM subscribers
          • 0

        Best

        D