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!

[Alekh · Accepted solution]

@grazitti_sapna I have 2019.2.5.62427 (admin) where the workflow works, but it doesn't work on 2019.1.6.58192 (non admin). 

It's repeatable for me so potentially could be a bug.

EDIT: I realised that it's because I have custom formulas. YEAR() and DATEADD() are custom ones from here. Workflow attached in my previous response should work. Just changed dateadd() to datetimeadd() and year() to datetimeyear().

[ailee15]

It did! Thanks a lot!

[AndrewBanh]

Hi @ailee15 

Without checking the check boxes in the crosstab tool, your results pane would look like:

However, if you do check those boxes, you will capture those details (contract start and end date) as seen below.

[ailee15]

oh, I get it now. thank you. 

Actually this question was a follow up from my other post (https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Split-over-contract-period/m-p/433621#...)

I have a workflow that does the rest of calculation that I mentioned in the link and realised i'd prefer to do the # of months calculation in WF as well. 

Now i don't know how to combine the two. Can someone help me pls?

Thank you!

[Alekh]

I added in my piece of the workflow with the other with some minor tweaks.