Flag percent of change when it goes negative

deona720
deona720 Member
edited January 30 in Magic ETL

Hello, as the title says I am trying to figure out a beast mode or add an additional column in a view/etl to flag a member when they logged in fewer times this current month compared to the previous month. I manually upload the month's numbers at the end of the month so we are measuring trends and wanting to reach out to members that stopped logging in as much.

Here is what my data should look like:

Member

Login Date

Login Value

Banana

12/1/23

6

Banana

1/1/24

10

66% Increase

Pear

8/1/23

4

Pear

12/1/23

10

Pear

1/1/24

2

80% decrease

Lemon

12/1/23

9

Lemon

1/1/24

10

11% Increase

Lemon

7/1/23

6

Kiwi

2/1/23

0

I want it to grab just the previous month to calculate the percent in change but also highlight any that show a decrease. I think I would like to make a metric card that shows like "7 decreases this month" and have it drill down to a table of those decreases for reference.

Thanks in advance!

Tagged:

Answers

  • ST_-Superman-_
    ST_-Superman-_ Coach

    @deona720

    Are you looking for help calculating the percent change? Or is that already part of your data and you are just looking for help building the card?


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • marcel_luthi
    marcel_luthi Coach

    Agree with @ST_-Superman-_ , we need to know the part you're currently struggling with. Also on the sample that you shared I see not all members have entries for every month (like Pear that has one for August and then none until December) you'd need to define how that should be handled and what to do when things go from non-existing the previous month to existing in this one and vice-versa.

  • GrantSmith
    GrantSmith Coach

    You can use a beast mode and the lag window function to calculate the percentage difference:

    (`Login Value`
-
lag(`Login Value`,1) over(PARTITION BY `Member` ORDER BY `Login Date`)
)
/
lag(`Login Value`,1) over(PARTITION BY `Member` ORDER BY `Login Date`)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • deona720
    deona720 Member

    @marcel_luthi Sorry, they actually should all have values for each month, some would just be 0.

    @ST_-Superman-_ Honestly, I am not sure where to start with this one. I don't know how to calculate just the percent of change, I think I could figure out the rest from there!

Categories