Discussions
Insight OBIEE - Tips & Tricks
With Insight, users can easily create ad-hoc analyses, drill down into data, and visualize results through a variety of charts and graphs.
Here are a couple of easy to apply formulas and functions for your daily contact, email and form reports:
- Comparing two date fields and using CAST function to remove the timestamp.
If you’re interested in comparing two date fields, for example, check if a contact has been created in the contact table and in your main CDO in the same day, ignoring the timestamp value, the following New Calculated Measure SQL Case Statement can be of help.
Example: CASE WHEN (CAST("Column1" AS date) - CAST("Column2" AS date)) < -1 THEN 'New Contact’ ELSE 'Old Contact' END
- Removing blanks or certain values in cells when filtering form submission data columns
The following SQL filters can be used to clean the form submission data report when the report returns too many blank cells within the fields.
Example: SQL Filter: "Metrics"."Form Field Value" != ' '
SQL Filter: "Metrics"."Form Field Value" NOT LIKE 'your value’
- Retrieving data with the last month, year
Usually when pulling reports, the ask is to pull the data over a full month or year.
The following SQL statements are an example how to pull data in the past month on the Email Send Date Attribute:
- First day of current month (use with "less than" filter):
TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH(CURRENT_DATE) +1 , CURRENT_DATE)
- First day of previous month (use with "greater than or equal" filter):
TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
In the last year
- First day of current year (use with "less than" filter):
TIMESTAMPADD(SQL_TSI_DAY, EXTRACT(DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)
- First day last year (use with "greater than or equal" filter):
TIMESTAMPADD(SQL_TSI_YEAR , -1, TIMESTAMPADD(SQL_TSI_DAY, EXTRACT(DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
I hope you find these useful!
Regards,
Marius
Marketing Automation Expert @ Logarithmic Solutions