Find missing quarter values

[pachopra]

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

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

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

[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

[kathleenmonks · Accepted solution]

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!

[Felipe_Ribeir0]

Hi @pachopra 

Please see the attached workflow. I used the average per year, but you can modify to median inside the macro.

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

Thanks.

[Felipe_Ribeir0 · Accepted solution]

Hi @pachopra 

It worked?