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 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.
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!
The configuration for Custom Date 03 is missing a conversion to being of the expected data type. Where you have the data_select value set to MAX(key_value), I've updated it to MAX(CAST(key_value AS DATETIME)). The null_value, which is applied to constituents who have no results for a given constituent element was set to "tbd", so I updated that to 1900-01-01, which is the default placeholder date in Analytics for empty date fields. I've since restarted your load and has proceeded past the previous point of failure.
Constituent division elements are updated nightly based on the Last Activity Date on the constituent record. The Last Activity Date is updated on order save, so an update to a custom field on an order in the application is expected to also update the Last Activity Date on the constituent record.
Custom Order Fields in the application do support date and numeric as well as text data types. They are stored in the Tessitura database as text, but all places that present them do so with a conversion from text to the configured data type for the custom field. Analytics does include all those custom fields regardless of data type, however stores and allows reporting on them only as text. There is a roadmap item to do a similar data conversion during the nightly load that the Tessitura application does on the fly so that these custom fields can be used in the context of the data type for which they are configured in Tessitura.
Therefore, changing your keywords to be text data types in Tessitura is not necessary here.