Discussions
Insight (OBIEE) Tips | Removing the timestamp from a date
Have you wondered how to get rid of a timestamp in a date column? Here’s a method..
Here is a simple email report that shows the total sends on a given date.
Subject area: Email Analysis by Send Date
The problem is that i don’t want to see the total sends by day and time… I want to a total by day.
Let’s fix it!
Email Send Date vs Calendar Day attributes
First off, instead of Email Send Date, use the Email Send Calendar attribute called Day. Why? This attribute rolls up all emails sends by day. Whereas the Email Send Date is the date and time of the email send.
Here is the updated column selection:
When I look at the results though, it is still showing the time! Argh.
Remove the time
I know that some of you just love using SQL by now You can remove the time from the column using the CAST() function.
On the Criteria tab, click the Settings icon on the Email Send Calendar column. Then choose Edit formula.
Change the formula to:
CAST("Email Send Calendar"."Day" AS date)
Et voila! The time is removed!
Make the column heading reader friendly
When I added that CAST() function, it changed my column header. As a final cleanup, I’ll update the column heading.
On the Results tab, edit the table view.
In the Layout section, click the settings icon and choose the Format Headings option.
Change the caption to be the desired heading.
After applying the changes, here is the final report.
Remove the drill down interaction
Did you also notice that when I applied the CAST() function, the send date became a link?
This drill down link interaction doesn’t make sense in this report since the Day attribute is rolled up to midnight. So, let’s remove it.
Back on the Results tab, edit the table view. Then in the Layout section, click Settings > Column Properties.
On the Interaction tab, change the interactions to None.
And here are the final results! SHIP IT!
Final notes
- The date format (m/d/yyyy) comes from Eloqua’s Insight setup. To change the date format, you can apply your CAST() function and then update the column properties to use your desired format.
- However, if you do not want to use CAST() and simply try to override the Data Format using a custom date option, this is not supported by Insight. You will get an error when try to save the analysis.
Comments
-
Yes I have been trying to figure this out so this is great! Thank you @Alexa Kalapaca-Oracle!
-
HI Alexa,
How do I do this if only want to see M-YYYY? I'd prefer to see 05-2018 instead of (m/d/yyyy)
Thanks
Michelle
-
HI Alexa,
How do I do this if only want to see M-YYYY? I'd prefer to see 05-2018 instead of (m/d/yyyy)
Thanks
Michelle
Hi! You can do that for sure using the Column Properties.
* First perform your CAST() function
* Then open the Column Properties for the column you performed the CAST() on
* On the Data Format tab, choose the Override option, then the desired Date Format. Just remember, you can't use that Custom option.
-
Does anyone know how to display the month name? When I use "Email Send Calendar"."Month" it displays as YYYY-MM. I haven't been able to find any documentation or posts about displaying the Month Name.
-
How do you amalgamate the date data? All this does is remove the timestamp, but the data displays the same way with or without the timestamp. How do you achieve this?
-
How do you amalgamate the date data? All this does is remove the timestamp, but the data displays the same way with or without the timestamp. How do you achieve this?
I just found this out - if you are using Email Send Date from Email Send Date Attribute, even after you use CAST to remove the time stamp, Insight will not amalgamate your values together - they still remain on their own rows.
Use Day from Email Send Calendar instead, and use CAST to remove the 12:00:00 AM time stamp.
-
Does anyone know how to display the month name? When I use "Email Send Calendar"."Month" it displays as YYYY-MM. I haven't been able to find any documentation or posts about displaying the Month Name.
Did you try using "Email Send Calendar"."Month Desc"? Here is an example of how they look:
-
Hello! I am having some trouble with the removal of the timestamp per day and I was hoping someone could help me... I am trying to show the number of subscriptions per day and as you can observe below the TOTAL of contacts subscribed per day are divided into different rows depending on the time of the day instead of per day (i.e. see 07/05/2020 and 20/10/2020). Has anyone encounteres/solved this issue before?
-
hi, I think this was answered over here: https://community.oracle.com/topliners/discussion/4283450/insights-count-for-each-date
