How to create a automated generating rows within a table

[vpalani]

date             region   value%

2021-04-01  East       45

2021-04-01  West       55

2021-04-01  North     65

2021-04-01  South    75

I want to add rows below the table, as shown below without manually entering it

2021-05-01 East null

2021-05-01 West null

2021-05-01 North null

2021-05-01 South null

2021-06-01 East null

2021-06-01 West null

2021-06-01 North null

2021-06-01 South null

I need to do this because a quarter is of 3 months and we consider only first day and first month of a quarter.

Any help is much appreciated

[Jean-Balteryx]

Hi @vpalani ,

How far do you need to generate this data ? Till the end of the quarter ?

[vpalani]

I need this to be generated for each quarter...
For example :
When we are in current Q3, we need Q2 data.
Our data currently has values like;
2021-04-01 (comprising of all month (April, May, June) considered as Q2)
2021-01-01  (comprising of all month (Jan, Feb, March) considered as Q1)

So let's consider Q2, I need to create rows automatically for Months May and June with null as value across each region 

So for every Quarter I need to create extra rows as shown below;

2021-05-01 East null

2021-05-01 West null

2021-05-01 North null

2021-05-01 South null

2021-06-01 East null

2021-06-01 West null

2021-06-01 North null

2021-06-01 South null

I hope this helps you

[dfurlow]

Depending on your answer to @Jean-Balteryx 's question you maybe can use the formula tool with the DateAdd function to generate additional columns for 30, 60, or/and 90 days into the future and then use the transpose tool to get data back into your tall format...?

[Jean-Balteryx]

@vpalani , can you share a sample of your data with dummy values except for dates please ?

[vpalani]

I have attached it

[Jean-Balteryx]

You attached pictures, can you send an Excel file ?

[vpalani]

Attached the excel

[Jean-Balteryx]

Here is how you can do it ! Tell me if it suits your need or not !