How to add months that has no data?

[JoRao]

Hello - 

I am trying to force add Jan and Feb to the month field. For example, Group XYZ only has data for the month of March, but I need to show 0's for Jan and Feb. or if a group has data for Jan and Feb but nothing in March, I will need to march in but show 0's. How do I accomplish this? Also, I want to be able to scale it to any other group that may show up in Apr but has no values in Jan, Feb or Mar.

Group Name	Month	A	B	C	D	E	F
ABC	FEB	0	0	-3	0	0	-3
ABC	JAN	0	1	-3	1	-2	-3
ABC	MAR	0	0	-8	0	-2	-10
DEF	FEB	0	7	-3	0	0	4
DEF	JAN	0	32	-41	2	-6	-13
DEF	MAR	0	2	-8	0	-1	-7
XYZ	MAR	2	0	0	0	0	2

Thanks a lot in advance!

Best,

Jo

[TrevorY · Accepted solution]

I've constructed the attached workflow that shows how you might go about creating a check to make sure that the correct number of months is included. This workflow uses your exact text to make sure that Feb, Jan, and Mar are all present for each group. If you wanted to scale up and add April (or subsequent months), you can simply alter the filter to 4 months and add April in the text input. I hope this helps!

[JoRao]

Perfect! Worked like a charm!! Thank you so very much!!

Best,

Jo

[JoRao]

I have another question related to this post: Once I added the month field, how would I populate the Group ID and Group Category? I tried using the Multi-row formula with an If then statement, but that did not work. Thanks!

Group Name	Month	Group ID	Group Category	A	B	C	D	E	F
ABC	FEB	1234	Office Supplies	0	0	-3	0	0	-3
ABC	JAN	1234	Office Supplies	0	1	-3	1	-2	-3
ABC	MAR	1234	Office Supplies	0	0	-8	0	-2	-10
DEF	FEB	4567	Paper	0	7	-3	0	0	4
DEF	JAN	4567	Paper	0	32	-41	2	-6	-13
DEF	MAR	4567	Paper	0	2	-8	0	-1	-7
XYZ	FEB	Null	Null	Null	Null	Null	Null	Null	Null
XYZ	JAN	NUll	Null	Null	Null	Null	Null	Null	Null
XYZ	MAR	8910	Furniture	2	0	0	0	0	2
GHI	JAN	1112	Electronics	3	4	-5	1	2	3
GHI	FEB	1112	Electronics	1	2	3	4	5	6
GHI	MAR	Null	Null	Null	Null	Null	Null	Null	Null

[TrevorY · Accepted solution]

I would create a key in the form of a text input that has each group name and its corresponding group ID and group category. I would then join that key data in at the end of this workflow to make sure that these fields are joined in for each group name (join on group name). You can also use a formula tool to write an if then statement for the group ID and group category fields, separately. 

[JoRao]

Thanks again @TrevorY!!

[TrevorY]

No problem!