Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Number of months in a period in each year

ailee15
6 - Meteoroid

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!

 

 

14 REPLIES 14
Alekh
8 - Asteroid

@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
6 - Meteoroid

It did! Thanks a lot!

AndrewBanh
9 - Comet

Hi @ailee15 

 

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

 

Without check box.PNG

 

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

With checkbox.PNG

ailee15
6 - Meteoroid

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
8 - Asteroid

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

Labels