Hello all,
I feel like this should be an easy solution (I do this in Excel all the time), but I'm struggling to get anything to work in Analytics.
I have a Pivot Table with Student name in one column and Date of Birth in another. I am trying to add a third column where the age is calculated,
I normally do this in Excel with =((today's date - DOB)/365), and am struggling to translate this to a usable function in Analytics.
Any ideas? Anyone who has a solution that already works?
Thank you!
Could you use the ydiff() function?
I tried that and got a constant error, not sure what i'm missing or doing wrong. Let me send screenshot.....
With [Constituent ID] included on Rows of a Pivot widget, this will be approximate ...
MAX( YDIFF ( NOW ( [Years in Birth] ) , [Years in Birth] ) )
... and this will be closer if not exact ...
MAX( DDIFF ( NOW ( [Days in Birth] ) , [Days in Birth] ) ) / 365
With that you can also look at bucketing results, where Constituent ID is not a field in the widget, here's an example for constituents in their 20s.
SUM ( [Constituent ID] , IF ( MAX( DDIFF ( NOW ( [Days in Birth] ) , [Days in Birth] ) ) / 365 >= 20 AND MAX( DDIFF ( NOW ( [Days in Birth] ) , [Days in Birth] ) ) / 365 < 30 , 1 , 0 ))
And lastly you could consider configuring a custom Analytics Constituent Division Element with a custom Range Type for easier use in Analytics. See this Analytics Technical Overview, Part 2 Configuring System Tables starting at 8:17 for context, and Age Range starting at 22:34.
Thank you, I will look further into the Technical videos.
I tried
MAX( DDIFF ( NOW ( [Days in Birth] ) , [Days in Birth] ) ) / 365 and I'm getting the same exclamation mark error. My Days in Birth is pulling from the Order Custom Fields, where custom field 01 is a date.