Alteryx Designer

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

Number of months in a period in each year

Highlighted
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!

 

 

Highlighted
Alteryx Partner

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

Highlighted
Alteryx Partner

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

Highlighted
Alteryx Partner

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

 

Highlighted
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

Alteryx Partner

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.

Highlighted
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?

Highlighted
6 - Meteoroid

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

Highlighted
Alteryx Partner

Hi, 

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

 

Thanks.

 

Capture.PNG

Highlighted
Alteryx Partner

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