Using rank within a window function to filter results

I am trying to analyze the initial purchase behavior of 2 subsets of users, mobile app users and non mobile app users.
I have client purchase history in a table where each row is a sale and contains whether the user is an app user or not and I've created a beast mode that uses variables "min sale date" and "max sale date" to filter the sales and determine the average spend per client.
First I calculate the total sales:
sum(CASE WHEN `Date` >= `Min. Sale Date` AND `Date` <= `Max. Sale Date` THEN `Total` END END)
Then I calcuate the number of clients:
COUNT(DISTINCT (CASE WHENcreated_at_clients
>= `Min. Sale Date`At
ANDcreated_at_clients
<= `Max. Sale Date` THENunique_mb_on_sale
END))
Then I simply divide the first by the second to achieve the avg. spend per client for the given date range.
I want to be able to filter this equation so that it only includes the client's first purchase so I did the following:
CASE WHENDate
>=Min. Sale Date
ANDDate
<=Max. Sale Date
THEN
RANK() over (PARTITION BYunique_mb_on_sale
ORDER BYDate
)
END
However when I try to filter based on this rank value it does not work. Somehow everything has a rank of 1 when plotting the avg. sale per client on the y-axis and app user vs. non app user on the x-axis. Is there any way around this?
Answers
-
@user060355 Are you using Beast Mode or Magic ETL? If you are using Beast Mode, filtering by a rank will cause an error because their results are dynamically calculated in the card. This will have to be done in Magic ETL using a combination of a group by a constant (first two calculations) and a Rank and Window tile (third calculation). If you share some screenshots of how you currently have this set up I could give some more specific feedback.
0 -
@MichelleH the issue there is that I want the rank to by dynamic based on the date range being filtered by the user. If I rank in the ETL, the date would be unknown. Is there a way around this or maybe a different filtering method to filter the minimum rank rather than rank = 1 since regardless of window, the lowest rank would always be the initial purchase?
0
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Connect
- 1.3K Connectors
- 308 Workbench
- 7 Cloud Amplifier
- 10 Federated
- Transform
- 662 Datasets
- 118 SQL DataFlows
- 2.2K Magic ETL
- 821 Beast Mode
- Visualize
- 2.6K Charting
- 85 App Studio
- 46 Variables
- Automate
- 193 Apps
- 483 APIs & Domo Developer
- 85 Workflows
- 23 Code Engine
- AI and Machine Learning
- 23 AI Chat
- 3 AI Projects and Models
- 18 Jupyter Workspaces
- Distribute
- 116 Domo Everywhere
- 283 Scheduled Reports
- 11 Software Integrations
- Manage
- 142 Governance & Security
- 10 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 115 Community Announcements
- 5K Archive