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.....
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.
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?
Yes, however the division element is expected to produce one row per constituent. If a constituent has many orders with different values for the birthdate custom order field, the definition for the division element will need to be designed to take only one. For example, using something like this for the data_select value:
MAX(CAST(custom_1 AS DATE))
Tessitura v15 Help System - TX_ANALYTICS_DIVISION_ELEMENT (tessituranetwork.com)
Hi Chris - "Can it be moved/duplicated to be a Custom Date constituent element?" - I feel crazy, but where do Custom Date constituent elements live on the Constituent?
Hi Katie,
You're fine... These Constituent Division Elements are specific to Analytics and are not populated back into the Tessitura database. They are configured in TX_ANALYTICS_DIVISION_ELEMENT, but aren't visible in the Constituent record... only in Analytics.
Hi Chris,
I don't want to get too in the weeds here, I feel like we're really going down a circular rabbit hole. I opened a ticket about this a few days ago. I did originally create Analytics Division Elements based on the Custom Order Fields, and they only work part of the time - they will populate one day, and then not the next. I didn't know at the time that Custom Order Fields only support text? (Is that correct)? I changed all my keywords in our database to alphanumeric last night to see if that changed anything this morning (but on top of ALL of this, my database didn't refresh last night? It last updated on the 25th at 12:30am). So i can't see if any of my changes I worked on last night did anything. I don't know if you have visibility into help tickets, but you could read the detail of mine at Ticket [699162]. THANK YOU!