Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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