Doing a moving average using window functions in Analyzer
I have this salesforce lead historical dataset (which tracks Lead stage changes) and I want to show a monthonmonth moving average of the number of days it takes a lead to convert to the next stage (e.g. 30 days from Stage 1 to Stage 2) with Stage as series/dimension in my card.
The dashboard/cards that I'm creating is filterable by multiple fields so my best option is to use the window function in Analyzer (so the card values dynamically changes based on filter selection). My main problem is that there are valid duplicate rows in my dataset as 1 lead can be tagged to 1 or more teams.
To illustrate, below is an example of my dataset where Lead01 is tagged to 2 Teams. I tried using the formula below to get the moving average but the issue here is it includes the duplicate 30 days from Lead01.
AVG(AVG(`No. of Days to Convert`)) OVER (PARTITION BY `Stage` ORDER BY `Month`)
My desired output is for the month of March (based on Stage Exit Date), the average no. of days to convert for Suspect stage should be 25 days (average of 30 & 20). In the same month, when I filter Team=A, avg. days to convert is 25 and for Team=B, it should be 30.
I can't seem to get the output that I want with my current dataset. Is there another way to do this? Thanks!
Best Answer

@nicangelica , at the moment you should restructure your data to get the desired outcome.
to calculate that avg dynamically w/o preaggregating your data or doing funky math, your best choice is to:
1) APPEND a version of the dataset to the table where the metric is not duplicated to the full dataset where the team column contains 'All Teams' and the rows have been deduplicated. (functionally you'd double the rows in your dataset) This would require you to apply a filter, but the default team filter would be 'All Teams', and then when you filter down to a specific team you still se all the data.
2) add the metric (no. days to convert) as a column for each team days_to_convert_teamA, days_to_convert_teamB (this is obviously not desireable but still an option).
Jae Wilson
Check out my π₯ Domo Training YouTube Channel π¨βπ»
**Say "Thanks" by clicking the β€οΈ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Answers

I am not sure what you're trying to achieve here. But if you want the averages for both teams, shouldn't you partition it by teams as well?
if you want the dataset to be filterable on teams and want to exclude a duplicate only during summarization, that won't be possible. You could partition it by teams and then show averages of each team on the series of the chart.
Right now, if you filter on Team B do you not get 30 as the output?
Also what happens if you just use: AVG(`No. of Days to Convert`)) OVER (PARTITION BY `Stage` ORDER BY `Month`)
0 
Hi @nicangelica
What you're looking to do isn't quite possible since you'd need to do a window function inside a window function to determine if that record is duplicated or not within your dataset.
The card will fail to load if you're using any other aggregation on the other datasets. While that is correct SQL syntax (minus the extra ')' ) Domo processes the information a bit differently. It runs the window function before any aggregation happens so each record has a value. If your card then has a SUM or MIN etc on another column it will them do that after the window function has been calculated. But because the window function returns a single value for each row Domo doesn't know how to process all of those rows and throws an error, that's why you need the extra aggregate around your window function.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
Hi @GrantSmith.
I did have the same idea but I guess it just really is impossible with my dataset, but thanks for letting me know! It saved me the time to think of the endless solution to this problem.
0 
@GrantSmith Can you not put an avg on the beast mode in the card itself? like avg after dragging the field onto the values placeholder.
0 
Not currently as it thinks it's already an aggregate.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
@nicangelica , at the moment you should restructure your data to get the desired outcome.
to calculate that avg dynamically w/o preaggregating your data or doing funky math, your best choice is to:
1) APPEND a version of the dataset to the table where the metric is not duplicated to the full dataset where the team column contains 'All Teams' and the rows have been deduplicated. (functionally you'd double the rows in your dataset) This would require you to apply a filter, but the default team filter would be 'All Teams', and then when you filter down to a specific team you still se all the data.
2) add the metric (no. days to convert) as a column for each team days_to_convert_teamA, days_to_convert_teamB (this is obviously not desireable but still an option).
Jae Wilson
Check out my π₯ Domo Training YouTube Channel π¨βπ»
**Say "Thanks" by clicking the β€οΈ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 
hi @jaeW_at_Onyx, you're awesome! Thanks for laying out these options. I think option#1 would be my best choice since the 'Team' field contains more than 20 values (and might increase/decrease in the future).
0
Categories
 All Categories
 1.2K Product Ideas
 1.2K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 273 Workbench
 2 Cloud Amplifier
 3 Federated
 2.7K Transform
 78 SQL DataFlows
 527 Datasets
 2.1K Magic ETL
 3K Visualize
 2.2K Charting
 441 Beast Mode
 23 Variables
 514 Automate
 115 Apps
 391 APIs & Domo Developer
 8 Workflows
 26 Predict
 10 Jupyter Workspaces
 16 R & Python Tiles
 332 Distribute
 77 Domo Everywhere
 255 Scheduled Reports
 67 Manage
 67 Governance & Security
 1 Product Release Questions
 Community Forums
 41 Getting Started
 27 Community Member Introductions
 68 Community Announcements
 4.8K Archive