Flag percent of change when it goes negative
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!
Answers
-
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.” -Superman1 -
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.
0 -
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!**1 -
@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!
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 49 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive