Forum Discussion

ivandelgra's avatar
ivandelgra
Icon for Helper I rankHelper I
6 years ago
Solved

Count Status change Year over Year

Hello I have this dataset:   INDEX ID PERSON CARD TYPE SUBSCRIPTION YEAR 1 A FREE 2018 2 B PREMIUM 2018 3 C FREE 2018 4 D PRO 2018 5 A PREMIUM 2019 6 B PRO ...
Get Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Minor change required:

     

    Change = 
    VAR __Current = [CARD TYPE]
    VAR __Previous = 
        FILTER(
            'Query1',
            [ID PERSON] = EARLIER([ID PERSON]) && 
                [INDEX] < EARLIER([INDEX])
        )
    VAR __LastID = MAXX(__Previous,[INDEX])
    VAR __Last = IF(ISBLANK(__LastID),BLANK(),MAXX(FILTER('Query1',[INDEX] = __LastID),[CARD TYPE]))
RETURN
    SWITCH(TRUE(),
        ISBLANK(__Last),BLANK(),
        __Current <> __Last, __Last & " to " & __Current,
        BLANK()
    )

     

    I don't typically use an Index starting at 0 because of issues like this.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Minor change required:

 

Change = 
    VAR __Current = [CARD TYPE]
    VAR __Previous = 
        FILTER(
            'Query1',
            [ID PERSON] = EARLIER([ID PERSON]) && 
                [INDEX] < EARLIER([INDEX])
        )
    VAR __LastID = MAXX(__Previous,[INDEX])
    VAR __Last = IF(ISBLANK(__LastID),BLANK(),MAXX(FILTER('Query1',[INDEX] = __LastID),[CARD TYPE]))
RETURN
    SWITCH(TRUE(),
        ISBLANK(__Last),BLANK(),
        __Current <> __Last, __Last & " to " & __Current,
        BLANK()
    )

 

I don't typically use an Index starting at 0 because of issues like this.

ivandelgra's avatar
ivandelgra
Icon for Helper I rankHelper I
6 years ago

Greg_Deckler YOU ARE A GENIOUS!!! WOOOW! 

 

I have made some minor change, too in order to include the result for people that does not change YoY.

 

Below you can find the final formula. thank you Greeeeeeeeeeeeg

 

Change =
VAR __Current = [CARD TYPE]
VAR __Previous =
FILTER(
'Query1';
[ID PERSON] = EARLIER([ID PERSON]) &&
[INDEX] < EARLIER([INDEX])
)
VAR __LastID = MAXX(__Previous;[INDEX])
VAR __Last = IF(ISBLANK(__LastID);BLANK();MAXX(FILTER('Query1';[INDEX] = __LastID);[CARD TYPE]))
RETURN
SWITCH(TRUE();
ISBLANK(__Last);BLANK();
__Current <> __Last || __Current = __Last ; __Last & " to " & __Current;
BLANK()
)
 
  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago
    You're welcome! Happy to help!