How to create a prompt, Year-Month format, in analysis while not having a "Month" dimension?

Jonathan Chan gmg

Summary

The goal is to enable end users to look at an analysis by year-month periods (ex. YYYY-MM, 2020-01), but "Month" dimension is not available within the subject area.

Content

Hello Oracle Community,

Do you know in analysis whether there is a method to create a Year-Month format (YYYY-MM, ex.2020-01) prompt without the availability of a Year-Month dimension within the subject area?  Condition displayed in enclosed screenshot.

The goal is to enable end users to view an analysis in different year-month periods by selecting them on a drop-down prompt.  In the subject area, the only date type dimension available is "Account Established Date", that comes in DD/MM/YYYY format.  Dropping this dimension in prompt section only limit selection to each event date, but not at a upper tier of "Year-Month".

I do not have access to edit RPD.  Time dimensions from other subject areas could not be "lend" to this subject area because there are no other subject areas that could be successfully connected to this subject area with the  "Show Total value for all measures on unrelated dimensions." option under "Advanced SQL Clauses".

Thank you.

 

 

 

 

Version

Oracle Business Intelligence 11.1.1.9.0

Dimple-Nagesh-Support-Oracle

Hi Jonathan,

  You can edit the colum Formula for "Customer Account Details"."Account Established Date" and add the below 

CAST (YEAR("Customer Account Details"."Account Established Date") as CHAR) ||  ' - '  || CAST(MONTHNAME("Customer Account Details"."Account Established Date") as CHAR)

Hope this helps.

 

Jonathan Chan gmg

Hello Dimple Nagesh,

Works perfect.

Further question, is there a way to set the columns (or write a formula) so they are dynamic to the filter month apart from using (FUNCTION...by...) operation?  For example, the "Rank (By Brand)" column will automatically reflect the subject ranking per the Year-Month selected in the filter, instead of all data (from all months) within the database.

Thank you.

Dimple-Nagesh-Support-Oracle

Hi Jonathan,

   Are you asking, if you have any dimension from where you can get month directly ?

 

Jonathan Chan gmg

Hello Dimple,

Apologize for my delayed response.

No, with regards to the followup question posted on 31-Mar.

Now I want to find out if there is a way to set the selected columns so they are in sync with filter-prompt results, without having to write formulas involving the "by" command.

For example, to create a column that shows the number of new customer account opened per brand per sales person, the following formula ensues:

COUNT("Customer Account Details"."Customer Account Number" BY "Customer Account Details"."HZ_CUST_ACCOUNTS_CUSTACC12_", "Customer Account Details"."HZ_CUST_ACCOUNTS_CUSTACC18_")

Dimension index:
1) ...Account Number = Customer Account Number
2) ...ACCOUNTS_CUSTACC12 = Brand code
3) ...ACCOUNTS_CUSTACC18_ = Sales person code

I wonder is there a way in which "...ACCOUNTS_CUSTACC12" (Brand code) does not need to be included in the formula, and account number count will automatically be calculated when filter-prompt is filter to a particular brand?  Currently, the column will sum the total amount of new customer account across all brands if command "...by...ACCOUNTS_CUSTACC12" is not included, despite filter-prompt is set to show the result of one single brand only.

Thank you.

 

 

 

 

 

Dimple-Nagesh-Support-Oracle

Hi Jonathan,

  Good Morning!!
The formulae COUNT("Customer Account Details"."Customer Account Number" BY "Customer Account Details"."HZ_CUST_ACCOUNTS_CUSTACC12_", "Customer Account Details"."HZ_CUST_ACCOUNTS_CUSTACC18_") is getting the count of Number of Customers (Customer Account Number) based on BU's (HZ_CUST_ACCOUNTS_CUSTACC12_)

The BY clause is used to perform the aggregation at specified level.
And when you remove the HZ_CUST_ACCOUNTS_CUSTACC12_ from the formuale, it will provide the count of Number of customers based on HZ_CUST_ACCOUNTS_CUSTACC18_

You may get more details on Alternative Syntax: https://docs.oracle.com/middleware/bi12214/biee/BIESQ/toc.htm#GUID-E4D7E4A8-FB76-422C-B517-1E1C1360F0D0

Jonathan Chan gmg

Hello Dimple,

Thank you for the detail explanation.

Will look to the alternative syntax for more references.

Stay healthy and safe.