nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

OAS Prompts in Dashboard

VijayDC

HI All,

We have a dashboard /analysis with month and Year prompt, but when user selects a month prompt, Analysis should display data for the selected month and previous 3 months too.

we are using OAS 7 on Linux with Oracle DB 19c.
Please help how we can achieve this.

Find more posts tagged with

OBIEE

OAC

OAS

Accepted answers

Federico Venturin - Oracle

Hi @VijayDeenaNathChouhan ,

I'd start by defining a presentation variable for each prompt (e.g. PV_Year and PV_Month).

Then, I'd create a new calculated column in your analysis Criteria as follows (assuming that both Year and Month are string, otherwise you have to convert them accordingly):
CAST("YearColumn" || - || "MonthColumn" || '-01' as DATE).

Finally, I'd create a filter for the calculated column as follows:
CAST("YearColumn" || - || "MonthColumn" || '-01' as DATE) <= CAST('@{PV_Year}' || - || '@{PV_Month}' || '-01' as DATE) AND CAST("YearColumn" || - || "MonthColumn" || '-01' as DATE) >= TIMESTAMPADD(SQL_TSI_MONTH, CAST('@{PV_Year}' || - || '@{PV_Month}' || '-01' as DATE), -3)

All comments

Federico Venturin - Oracle

Hi @VijayDeenaNathChouhan ,

I'd start by defining a presentation variable for each prompt (e.g. PV_Year and PV_Month).

Gianni Ceresa

Hi @VijayDeenaNathChouhan ,

Before to give you answers guessing everything, let's take a step back in understanding some details of your question…

First thing, what is the format of your year and month prompt (what kind of values are used there)?

When you say "display data for selected month and previous 3 months", do you mean that your measure should include the data for the 4 months? Or that your analyses should display 4 columns with the 4 months (selected + 3 previous)? What kind of visualization is used in your analysis? (A screenshot is often worth thousands of words).

And lastly, "when users selects a month prompt": what is expected to happen when a user doesn't select a month? Or is the month prompt mandatory and can't be empty? Is that prompt multi values or a single value?

Lot of questions, but the only way to give you a useful answer without guessing is to first understand your case correctly (otherwise it's like lottery: I could guess the winning numbers, but also not).

[Deleted User]

+1 to Gianni. Was writing the same draft but meeting (and Gianni's DMs) kept me occupied.

The question isn't "what formula do you need" but what are you trying to achieve how.

Never forget that a formula especially with such heavy logic will always be interpreted row by row, one by one, and depending on the quality of your data source, the model etc, will come with performance costs.

I'll do Gianni one better and even include: Do you just want a list going back 3 months? Or is your real business requirement something like KPIs from 3 months ago, compared to 3 months ago, 3 months from point in time rolling,…

Your question can be interpreted many ways and you need to think about what your goal is as well as how you achieve that goal. The old adage of "if your only tool is the hammer every problem will look like a nails" still applies.

VijayDC

@Gianni Ceresa
thank you for the reply
I want to setup this in classic BI,
By default, data for all the years and months is displayed on Tabular format.
as shown in the screenshot below if I select March I like to display Previous 3 months (including previous year's December months too)
if I select April then Month and dollar columns should display data for current year Jan, Feb, March and April (in rows).

Do I need to create extra column in BMM layer (using time series function) for this or can this be achieved in Analysis too.

Gianni Ceresa

Thanks for posting the screenshot and describing the expected behavior, it makes it a lot easier :)

My next question is purely about the layout of the expected result: would you like to have the previous 3 months as columns being next to "Dollar" in your table? The result table would be "Region", "Year", "Month", "Dollar", "Dollar 1 month ago", "Dollar 2 months ago", "Dollar 3 months ago".

Or would you like to see them as rows? Therefore instead of having a table with 4 columns and 6 rows, you will have the same 4 columns but 6 + 6*3 rows (more or less depending on data existence) ?

The main difference is that for the first case, with columns, using time series calculations in your BMM layer could be the easier solution.

For the second case, with rows, it's more a matter of filter applied to your analysis.

VijayDC

Thank you for the reply @Gianni Ceresa

Yes I would you like to see them as rows..
a table with 4 columns and 6 + 6*3 rows.
Like to know how this can be achieved …

Thanks for the hint, 1st solution about BMM layer RPD change I will try to implement.. But like to know how it can be done on front end (analysis/dashboard).

[Deleted User]

Vijay can I just ask: Why only a month? Is there no real notion of time in that model? "April" always means April of the current year? No notion of year over year or similar historical analysis?

The approach with just a month name is analytically very limiting.

VijayDC

@Christian Berg -Oracle

This is only for my learning/understanding and for future implementation … I took a scenario and trying to implement the same

[Deleted User]

Especially if you're trying to learn then I suggest you familiarize yourself with the basic concepts of OAS and how analytics is done.

It's quite limiting and sometimes even hindering to look at this in a very narrow "I want to see data ABC" way. Concepts like time-variant filtering can be achieved in many different ways already just in Answers (which you're using) and then things step up a notch if you start looking at DV (Data Visualization) as well which especially does many time-related concepts much more native than Answers does.

VijayDC

@Christian Berg -Oracle

Ok, are you telling me that what I asked is not possible in classic BI, can be achieved in DV. This was one of the questions posed to me in an interview who claimed to have 18 years of experience in the field.
Obviously i was not able to answer and ask these suggestions/questions posted here in the heat of moment. I was trying to find an answer, and this was my last resort at it. I am good if there is no straight answer or if my question it self is wrong but no body should have to be belittled this way. I expect better in Oracle communities unlike responses we get on Social media platforms.