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

YearFrac with Basis 1

pgindi002
5 - Atom

Hi all, 

 

Is anyone able to help with how a YearFrac calculation in Excel can be transferred to Alteryx? I've seen posts that provide a YearFrac workflow, but those represent a Basis of 0 (30/360). I am looking to represent a Basis of 1 (actual/actual). 

 

Any guidance is appreciated! 

 

Thanks,

Paige

5 REPLIES 5
jdunkerley79
ACE Emeritus
ACE Emeritus

Interesting challenge.

 

First make sure Date1 > Date2 and flip if not

Then work out days in each year, something like:

DateTimeDiff(Left([Date1],4)+"-12-31",
             Left([Date1],4)+"-01-01",
             "days")+1

 

The expression:

IF DateTimeYear([Date1]) == DateTimeYear([Date2]) THEN
	[DaysInYear1]
ELSEIF DateTimeYear([Date2]) - 1 =  DateTimeYear([Date1])
       AND RIGHT([Date1],5) >= RIGHT([Date2],5) THEN
    IF [DaysInYear1] = 366 Then
        IIF(RIGHT([Date1],5) < '03-01',366,365)
    ELSEIf [DaysInYear2] = 366 Then
        IIF(RIGHT([Date2],5) > '02-29',366,365)
    ELSE
        365
    ENDIF
ELSE
	(DateTimeDiff(Left([Date2],4)+"-12-31",
                 Left([Date1],4)+"-01-01",
                 "days") + 1) / (1 + DateTimeYear([Date2]) - DateTimeYear([Date1]))
ENDIF

 will work out the basis for the calculation and then:

DateTimeDiff([Date2],[Date1],"days")/[AnnualBasis]

 

This will give the correct value for Actual/Actual

 

Sample workflow attached

pgindi002
5 - Atom

This is exactly what I needed! It works perfectly. Thank you so much for your help, and especially for the sample workflow.

jdunkerley79
ACE Emeritus
ACE Emeritus

You're welcome - happy to help

 

FláviaB
Alteryx Community Team
Alteryx Community Team

@pgindi002 I am moving your post to our Discussion Board so other users can find it in case they run into the same problem. In fact, don't forget to "accept as solution" 😉 

Flávia Brancato
pgindi002
5 - Atom

Thanks Flavia - I accepted the solution

Labels