Maybe I'm just misunderstanding what is happening here, but I'm trying to use the Custom Date values to put in a Birthday so that Development can have a dashboard of "Upcoming Birthdays."
Birthdays are stored as an Attribute (no problem), so I though I would just convert that attribute to a date and funnel it into Custom Date 02 in the Analytics data elements. Seems simple enough.
In the TR_ANALYTICS_ELEMENT table, I put convert(datetime, key_value, 101) in the Select, VXS_CUST_KEYWORD in the From, Keyword_no = 1 in the Where, and 1/1/2900' for the Default.
For testing, I just wanted to output a Constituent ID and the Custom Date 02 value into a Pivot Table just to see what populated. This I apparently cannot figure out how to do. The Pivot table wants to do some kind of math on the value. So, I though I'd just use a filter for any Custom Date 02 values for the month of May. Couldn't figure out how to filter by Month that field.
So, maybe I'm just not using this field correctly. Can anyone tell me how to get a birthdate into Analytics in a way that can be used by Development? Specifically, I want to eventually assign a List filter to a Dashboard that will show birthdays for members of a Development Officer's portfolio for the current month.
Make sure you're adding the Custom Date 02 field into the Rows panel of the pivot widget. When inadvertently adding Custom Date 02 to the Values panel, it will force you to do a unique count or use some other kind of aggregate function to get a value out of it.
Just not working for me Chris. That is what I thought too, but here is what I get when I try to add a Custom Date into the Rows field.
I still get the "Years, Quarters, Months, etc." values to pick from. I just want the raw date to make sure my data is correct before I jump into trying to get just the birthdays for a given month.
Select "Days" from that list.
I'm guessing I have no data in the custom field then. I'm getting Jan 1 1900 for all custom date fields for all constituent records. I'll post a ticket to see what is up since I don't have direct access to the cubes to see what is there.
Hi David,
The data source (cube) would just show what the dashboard shows. Instead, I recommend plugging your constituent element configuration into a testing query. I'm adding the "q" alias to the source table in the test, but confirm that's in your TX_ANALYTICS_DIVISION_ELEMENT as well.
SELECT q.customer_no,ISNULL( convert(datetime, key_value, 101), '1900-01-01' ) AS resultFROM VXS_CUST_KEYWORD qWHERE Keyword_no = 1
If that returns the results you expect, and your TX_ANALYTICS_DIVISION_ELEMENT does include the "q" alias in the data_from field, then the support ticket is the best next step.
Thanks!