Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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
AndrewBanh
9 - Comet

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
9 - Comet

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

 

2019-07-02 14_44_30-Alteryx Designer x64 - ContractLength.yxmd_.png

Alekh
9 - Comet

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

 

ailee15
6 - Meteoroid

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
9 - Comet

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

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

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

grazitti_sapna
17 - Castor

Hi, 

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

 

Thanks.

 

Capture.PNG

Sapna Gupta
Alekh
9 - Comet

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.

Labels