Selected periods and year total

Question

I need to display in a pivot table :

- the total for the months selected;

- the total for the year.

How could we achieve this please ?

asim cholas · Answer

Dear

Set the logic for selected period in criteria 1 and total for selected period in second criteria and  for 12 months in third criteria. Use union all and check if it works(Combine similar request feature)

like below

criteria1

column1                                         column 2

MonthName(Datecolumn)              amount

filter - Jan to March 2017

criteria 2

column1                                         column 2

monthname(Min(datecolumn) || ' to ' ||monthname(max(datecolumn))          amount

filter - Jan to March 2017

criteria 3

column1                                         column 2

Year(datecolumn)        amount

filter - Jan to end of year 2017 (use timestamp functions to derive end of year)

I did not test it. check it and let us know if any further info required.

Thanks

Aj

Gianni Ceresa · Answer

Where does the requirement come from?

I bet on an Excel file, right?

Just keep in mind OBIEE isn't Excel, things are done based on models and playing with attributes (dimensions) and measures (facts).

Jean-Pierre Hoedenaeken · Answer

Using the time dimension does not work completely. I give you an example related to accounts with budgets.

The user will use a period dashboard prompt to select some periods (e.g. 201701 to 201703).

Then I want to display :

- the account;

- the amount for period 1;

- the amount for period 2;

- the amount for period 3;

- the total amount for periods 1 to 3;

- the total amount for periods 1 to 12.

If I use a "Pivot Table", the problem is that I need to place the "Year Total" in the "Rows" area because if I put it in the "Measures" area, the "Year Total" measure will be repeated for each month.

If I use a "Table" view, the problem is that I cannot display the periods selected by the user in the dashboard prompt.

Tx for your help.