Flag percent of change when it goes negative

deona720 · 2024-01-30T15:15:35+00:00

There was an error rendering this rich post.

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!

Find more posts tagged with

Magic ETL

Beast Mode

Accepted answers

All comments

ST_Superman

@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?

marcel_luthi

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

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

deona720

@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!