Generate rows dynamically for month

Question

HI all,

I have a set of data where i need to generate a row if no data available for  that particular month. below is my input and output required.

i have a set of data which has to generate the row dynamically for next 12 month and where ever we dont have data just need to show count as 0.

in below case we have Calendar day whcih reads as August 2018 (8.2018).

next month it will change to 09.2018 to 08.2019 so i need to set it dynamcially to generate the row can anyone help on this?

Input

CodeNameLocCalendar DayCount1A4278.2018160 KG1A4279.2018100 KG1A42710.2018200 KG1A42712.2018100 KG1A4271.2019100 KG1A4272.2019200 KG2B4278.2018100 KG2B4279.2018200 KG2B42710.201860 KG2B42711.2018160 KG2B42712.2018480 KG2B4271.201920 KG2B4272.201920 KG2B4273.201920 KG2B4274.201920 KG2B4275.201920 KG2B4276.201920 KG2B4277.201920 KG3C42711.201823 KG3C42712.2018115 KG3C4271.2019113 KG3C4272.2019112 KG3C4273.2019121 KG

Output

CodeNameLocCalendar DayCout1A4278.2018160 KG1A4279.2018100 KG1A42710.2018200 KG1A42711.20180 KG1A42712.2018100 KG1A4271.2019100 KG1A4272.2019200 KG1A4273.20190 KG1A4274.20190 KG1A4275.20190 KG1A4276.20190 KG1A4277.20190 KG2B4278.201820 KG2B4279.2018200 KG2B42710.201860 KG2B42711.2018160 KG2B42712.2018480 KG2B4271.201920 KG2B4272.201920 KG2B4273.201920 KG2B4274.201920 KG2B4275.201920 KG2B4276.201920 KG2B4277.201920 KG3C4278.20180 KG3C4279.20180 KG3C42710.20180 KG3C42711.201823 KG3C42712.2018115 KG3C4271.2019113 KG3C4272.2019112 KG3C4273.2019121 KG3C4274.20190 KG3C4275.20190 KG3C4276.20190 KG3C4277.20190 KG

BenMoss · Accepted Answer

There's a quite simple solution to this and it uses the 'generate rows' tool.

Here we can use the datetimetoday() function to acknowledge the current month and then use the generate rows tool to get the 12 months that should be in our data stream for each group.

You can then do an append and a join to bring calculate what months are missing.

Then a union to bring your data together into the output.

See attached solution.

Ben