Doing a moving average using window functions in Analyzer

nicangelica

I have this salesforce lead historical dataset (which tracks Lead stage changes) and I want to show a month-on-month 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 Lead-01 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 Lead-01.

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!

jaeW_at_Onyx

@nicangelica , at the moment you should restructure your data to get the desired outcome.

to calculate that avg dynamically w/o pre-aggregating 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).

rahul93

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

GrantSmith

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.

@rahul93

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.

nicangelica

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.

rahul93

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

GrantSmith

@rahul93

Not currently as it thinks it's already an aggregate.

jaeW_at_Onyx

@nicangelica , at the moment you should restructure your data to get the desired outcome.

to calculate that avg dynamically w/o pre-aggregating 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).

nicangelica

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