Enter empty columns based on current month

I have a project where I have to accomplish this:

I have a PIVOT table with months in the columns as below:

I want to create empty columns until the most recent month that ended. For example, since the data I have is until Jan_2021 and today's date is 4/15/2021, I want two empty columns for Feb_2021 and Mar_2021.

In short, I want empty columns for all the months until the most recent month that ended.

Output

Time Series

Accepted answers

Maskell_Rascal

Hi @murthybhs

Here is a solution with a dynamic approach. It will always generate empty columns for anything missing based on date workflow is ran, and the minimum date found in your headers.

I've attached the workflow for you to try out.

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

Add Empty Columns based on Current Month.yxmd

Maskell_Rascal

Hi @murthybhs

Sorry about that. I forgot to add an additional join field to the Join Tool. If you add a second join on the field Company, it will solve the duplication issue.

Thanks!

Phil

Add Empty Columns based on Current Month V2.yxmd

All comments

apathetichell

This isn't a one tool solution - (if you want to automate - there is the cheapo solution below) - so my questions are:

1) Is this something you are going to be repeating on a regular basis.

2) Is there potential data for those columns?

Here's how I see it - you have columns with date like features. You pivot the columns into rows - date/time the name field (after replacing the "_") and then take the max/min using a summarize tool. Then you can generate dates for the months in between using generate row with your min as your initiation, your max in your condition and your formula with datetimeadd([rowcount],1,"months"] - pivot those and dynamic rename so you now have field names. Then union in the rows with your original data source - voila.

Obviously depending upon the answers to 1 and 2 and how many months you are doing this for - really determine if you want to do that - or just add two new blank columns in formula.

Maskell_Rascal

Hi @murthybhs

Here is a solution with a dynamic approach. It will always generate empty columns for anything missing based on date workflow is ran, and the minimum date found in your headers.

I've attached the workflow for you to try out.

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

Add Empty Columns based on Current Month.yxmd

CatheyH

Hi,

Some assumptions:

You want to populate empty columns for any missing month from the earliest data point

ie if your starting data set is Sep_2020, Nov_2020, Dec_2020, Jan_2020 then you'd want to include Oct 20, Feb 20 & Mar 20

You want zeroes rather than empty columns

Dates always be formatted as MMM_YYYY

Not totally pretty but I think it does what you need

If it solves, please mark as completed, although I can see the solution above is pretty much identical 🙂

Cathey

Screenshot 2021-04-15 172847.png

FillDateGaps.yxmd

murthybhs

Thank you for the reply. It looks like your workflow is multiplying all the values in the months by 3.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703