Oracle Transactional Business Intelligence
How to display count(new customer account opened) from lasy year's month-to-date in analysis
Summary
How to display the amount of new customer account opened in last year's month-to-date, together with this year's month-to-date data
Content
Hello Oracle Community,
In OBIEE, how can the amount of new customer account (dealer) opened from last year's month-to-date (MTD) be displayed together with the amount of new customer account opened MTD this year.
I have been able to display the amount of new customer account opened MTD this year per sales person in both per brand and across all brands, as well as showcasing their ranking. The result is illustrated in enclosed screenshot. However, last year's MTD data could not be displayed. It either comes out in error number or does not show up despite using the below formulas:
• Scenario 1: Error Number. Data reflecting this year's month-to-date instead of last year's month-to-date. Column name: "Last Year MTD"
FILTER(COUNT("Customer Account Details"."Customer Account Number") USING "Customer Account Details"."Account Established Date" BETWEEN TIMESTAMPADD(SQL_TSI_DAY, 1 - DAYOFMONTH(TIMESTAMPADD(SQL_TSI_YEAR, -1, CURRENT_DATE)), TIMESTAMPADD(SQL_TSI_YEAR, -1, CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_YEAR, -1, CURRENT_DATE) )
• Scenario 2: Data does not show up. Column name: "Last Year MTD (2)"
COUNT("Customer Account Details"."Customer Account Number" by "Customer Account Details"."HZ_CUST_ACCOUNTS_CUSTACC12_", CAST (YEAR("Customer Account Details"."Account Established Date") as CHAR) || ' - ' || CAST(MONTHNAME("Customer Account Details"."Account Established Date") as CHAR) )
For your reference, below index shows dimension definition, formula used and filter setting:
1) Dimension definitions:
i) Brand
"Customer Account Details"."HZ_CUST_ACCOUNTS_CUSTACC18_"
ii) Commission code
"Customer Account Details"."HZ_CUST_ACCOUNTS_CUSTACC12_"
2) Formula used to computer other dimensions used in the analysis:
i) New customer opened MTD by brand (This year). Column name: New Dealer Opened MTD (By Brand)
COUNT("Customer Account Details"."Customer Account Number" by "Customer Account Details"."HZ_CUST_ACCOUNTS_CUSTACC12_", "Customer Account Details"."HZ_CUST_ACCOUNTS_CUSTACC18_", CAST (YEAR("Customer Account Details"."Account Established Date") as CHAR) || ' - ' || CAST(MONTHNAME("Customer Account Details"."Account Established Date") as CHAR))
ii) New customer opened (MTD) overall, across all brands (This year). Column name: New Dealer Opened MTD (Overall)
COUNT("Customer Account Details"."Customer Account Number" by "Customer Account Details"."HZ_CUST_ACCOUNTS_CUSTACC12_", CAST (YEAR("Customer Account Details"."Account Established Date") as CHAR) || ' - ' || CAST(MONTHNAME("Customer Account Details"."Account Established Date") as CHAR) )
3) Prompts implemented:
i) Year-Month.
CAST (YEAR("Customer Account Details"."Account Established Date") as CHAR) || ' - ' || CAST(MONTHNAME("Customer Account Details"."Account Established Date") as CHAR)
ii) Brand
"Customer Account Details"."HZ_CUST_ACCOUNTS_CUSTACC18_"
4) Filter - It is implemented otherwise the analysis will run into error as a result of maxing out query line.
"Customer Account Details"."Account Established Date"
>= TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
Thank you.
Version
Oracle Business Intelligence 11.1.1.9.0
Comments
-
Can you can do this using the time series functions ago() and todate() assuming your time dimension is exposed as a presentation hierarchy?
Oracle® Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1) E10544-08 December 2014
D Logical SQL Reference
https://docs.oracle.com/middleware/11119/biee/BIEUG/appsql.htm#CHDHHGAJ -
Nathan,
Hello again and thanks for reaching out.
Unfortunately this subject area does not have a time dimension, let alone a time presentation hierarchy. Dimension "Account Established Date", (at the bottom of the subject area panel displayed on the screenshot) is the closest to a time dimension. Is there anyway to come up with a solution amid the limitations?
Best.
