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