Selected periods and year total
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 ?
Answers
-
there are multiple ways to achieve this,
One of them would be to create a column in the RPD e.g. "Sales at year level" <- setting the level in the time hierarchy to Year
The rest is usual columns and filtering
0 -
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.
0 -
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).
0 -
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
0
