4 Replies Latest reply on Jul 4, 2017 11:36 AM by asim cholas

    Selected periods and year total

    user647947

      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 ?

        • 1. Re: Selected periods and year total
          FTisiot

          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

          • 2. Re: Selected periods and year total
            user647947

            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.

            • 3. Re: Selected periods and year total
              Gianni Ceresa

              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).

              • 4. Re: Selected periods and year total
                asim cholas

                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