Find missing quarter values

Hi folks - I have a dataset which has missing quarters. How do I identify them and add those missing quarters as rows and put the values as 0 next to them For example . Also instead of 0 is it possible to put like an average of min and max values at the Account/Product level

See attached sample input and o/p required

Thank you

Alteryx - Query.xlsx

Help

Accepted answers

kathleenmonks

Hi @pachopra

I made the changes in the attached workflow and screenshot. I created the averages before adding the new rows and then if the row was new, I changed the value to the average. If this or any of the other responses help, please mark the solution! Thanks!

addQuarters.yxmd

Felipe_Ribeir0

Hi @pachopra

It worked?

All comments

Felipe_Ribeir0

Hi @pachopra

Please see if it is it.

I created a row for every combination of Account/Product/Quarter between the minimum and the maximum year of the dataset.

If the provided answer helped you to solve the problem/was correct, please accept it as a solution

Thanks.

MissingQuarters.yxmd

kathleenmonks

Hi @pachopra,

See the attached solution if you only want the quarters in between the existing quarters in your spreadsheet. The workflow checks if additional rows should be added using the Multi-Row formula tool and if so, it adds them and does a few calculations in a formula tool to make those rows equal to zero and makes sure the Quarter is accurate.

addQuarters.yxmd

pachopra

Hello thank you for the help. Thats exactly what I wanted. Is it also possible to extrapolate and fill the missing values with something more numerical like mean/average in other words add a mean of the previous and the following quarter which has actual values so in previous example missing values will be 1500 and 6000