Calculating Age in an Analytics Widget

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.....

Oooo, an aside - how did you all get birth dates into your analytics environment, was it a consulting request from Tessitura? We currently store ours in attributes and being able to import that over to Analytics would make so much of our reporting easier.

I tried the DDIFF function - 

DDiff([Custom 01],[Days in Date1])

Where Custom 01 is the date of birth that I have created as a custom item on the order "Custom Data" tab, and the Days in Date is the performance date. 

It keeps greenlighting the syntax, and then when it refreshes the widget, there is a red exclamation box over my widget with the text "Error Querying Your Data Model".

I tried the ydiff, and keep getting errors about dimension v. numerical expressions. 

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.

Hi Ashleigh, a few things to explain what I've been doing:

1. We generally store birthdays in attributes as well. There is a way to add birthdates to Analytics by using one of the Custom Categories set up in the system tables. 

It's in the table TX_Analytics_Division_Element

Mine happens to be in the Element Id called "Custom Category 4".

Division is Default Division, 

Data Select is MAX(key_value)

Data From is dbo.V_CUSTOMER_WITH_PRIMARY_AFFILIATES q join dbo.TX_CUST_KEYWORD t on t.customer_no=q.expanded_customer_no

Data Where is t.keyword_no=528      <-----528 being my T_KEYWORD for the DOB attribute 

I've added a bunch of Attributes to Analytics this way - the only problem is 1. you won't know for sure you did it correctly until the next day because of the 24 hour refresh time and 2. It won't say the attribute name in your widgets, you have to search for "Custom Category 4", and therefore keep track somewhere handy which custom category calls to which keyword. But you can then rename the column in your widget to "DOB", or whatever. 

Also - I work for our dance academy, so 99/100 times, I'm looking for an individual record on a household, because I'm looking for a child. I don't want a DOB attribute for A1 or A2, or a household, I need it for an affiliate. The explanation I gave in my previous comment will pull an attribute from the household, not an individual.

To get around this - I built a DOB field in the Custom Tab of an order. So whenever someone registers for a program (we have a handful of summer registrations coming in daily these days for summer) I go in and add the DOB to the order custom field. It's an extra step, but then I also add the child as the initiator and recipient. So that I can pull reports in Analytics that have an initiator (child) name tied to the DOB on the order. 

I just try to get parents to register kids separately, and keep their orders separate. 

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. 

I was expecting that this was being brought into Analytics via a TX_ANALYTICS_DIVISION_ELEMENT Custom Date field. Apologies, as I didn't understand where the data was being stored. Currently the Order / Contribution / Plans custom fields in Analytics are only available as TEXT values, and not as authentic numbers or dates. 

(+) Support for order, plan, contribution, and constituent custom field descriptions and data types in Analytics. - Ideas - Ideas - Tessitura Network

Can it be moved/duplicated to be a Custom Date constituent element?

Make a TX_ANALYTICS_DIVISION_ELEMENT Custom Date field that pulls from a Custom Order Field? Can we do that?