Number of months in a period in each year

[ailee15]

Hi, 

I have a period that I would like to split into number of months in each year. 

e.g. if the contract period is Jul 1, 2016 - Jun 30, 2020, there will be 6 months in 2016, 12 months from 2017-2019, and 6 months in 2020 where we earn revenue. I would like columns to show '6' under 2016, '12 under 2017, 2018, 2019 and '6' under 2020. 

How do I make it work in the workflow?

Thank you in advance!

[AndrewBanh · Accepted solution]

Hi @ailee15 

Please find attached a workflow which addresses your issue.

It just needed a bit of parsing and a little bit of logic to implement.

Please let me know if you have any questions about it : )

- Andrew

[AndrewBanh]

Also, configure your crosstab tool as below if you want to have the contract start and end date for each contract.

[Alekh]

Saw your post and felt competitive, so posted another solution. Mostly similar though 😉

[ailee15]

Hi @Alekh

Thank you for the WF. I encountered an error at the "Generate Rows" step. 

It says "Parse Error at Char(0): Unknown function "dateadd". How do I fix it?

Thanks again.

Ailee

[Alekh]

What version of Alteryx are you using? If it's an older version, you won't have dateadd. See if changing it to datetimeadd instead works.

[ailee15]

Hi @AndrewBanh, 

Thank you so much for looking into it promptly!

I tried checking the contract start and end dates but could not quite tell what it does to the WF. 

Are you able to enlighten me on how the output would be different by checking these pls?

[ailee15]

Hello~ i am using 2019.2.5. isn't it the latest version? 

[grazitti_sapna]

Hi, 

In loop expression instead of dateadd the function will be datetimeadd.

Thanks.

[Alekh]

There shouldn't be anything wrong with the version you're running. I'll have a look into it later. For now, I tweaked it slightly. See if it works now.