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
