6 Replies Latest reply: Aug 7, 2012 1:56 PM by 920386 RSS

    How can I use filters to show sequential quarters in a table - Heat Map

    920386
      I need to sum sales for this quarter and last quarter, then do a percent change calculation in one table in 11g

      It needs to look like this:

      Product X, Last Qtr, This Qtr, % Change
      Prod_123, $100, $150, 50%

      I've tried creating one analysis for "Last Qtr" and an identical analysis for "This Qtr" and then tried using each as a filter for the two sales amounts but it isn't working.

      I know this is possible as I've seen articles written about this - but no examples or screen pictures so far.

      Anybody know how to do this?

      Thanks in advance!
        • 1. Re: How can I use filters to show sequential quarters in a table - Heat Map
          Srini VEERAVALLI
          You might have a W_DAY_D table. Look for the column W_CURRENT_CAL_QTR_CODE.
          Using this column can create a flag like
          W_DAY_D.W_CURRENT_CAL_QTR_CODE = 'Current' then 'Y' else 'N' end
          Using this you can create measures for Current and Last Quarter. do the same for Year using column W_CURRENT_CAL_YEAR_CODE.

          Do let me know if you need any more info.

          Pls mark if helps
          • 2. Re: How can I use filters to show sequential quarters in a table - Heat Map
            920386
            yes we have the current and previous quarter codes, the question is how to use it to get a measure from the same fact in one row. Pivot table is not an option.

            So with the Current and Previous codes in the time dimension, how do you get one measure (Sales Amount) to appear on one row in two columns that can be used in a calculation? One column for previous quarter, one column for current quarter.
            • 3. Re: How can I use filters to show sequential quarters in a table - Heat Map
              Srini VEERAVALLI
              You can pull 'Sales Amount' for 2 times, you might have repository variable for current_quarter and previois_quarter
              for current use exp like
              case when quart(date)=current_quarter then 'sale amount' end
              do the same for previous quarter.

              You can implement the same in rpd or else answers.
              • 4. Re: How can I use filters to show sequential quarters in a table - Heat Map
                920386
                sorry but I still don't see how that would work.

                Create a new query - Start with "Fiscal Year" and Filter it for 2012. Add Fiscal Month number and filter it for "11;12", last add the "Sales Amount" fact. You should get a result with two rows, one for month 11 and one for month 12, each with a "Sales Amount" like this:

                Fiscal Year Fiscal Month Number Sales Amount
                2012 11 $100
                2012 12 $150

                The problem is that I need month 11 and month 12 as separate columns on the same row so I can do a calculation on them. Like this:

                Fiscal Year Month 11 Sales Month 12 Sales Month12/Month 11
                2012 $100 $150 1.50


                This is what I am doing that is not working:
                1) With the first analysis above - set the month filter to "11" and save the analysis as "Month 11 Sales Amount"
                2) With the first analysis above - set the month filter to "12" and save the analysis as "Month 12 Sales Amount"

                Now I have two analysis to use with the "is based on results of another analysis" filter in a third analysis. One filter anaysis for each month.

                3) With one of the above two analysis - remove the filters and save as "Month 12/Month 11 Sales Amount"
                4) Add another "Sales Amount" column to the analysis - so now we have a Fiscal Year, Fiscal Month Number, Sales Amount, Sales Amount columns
                5) Add filter to the first "Sales Amount" column using the "is based on results of another analysis" option and select the "Sales Amount" column of the "Month 11 Sales Amount" analysis
                This results in only the Month 11 sales amount being returned.
                6) When I add the Month 12 Sales Amount analysis as a filter for the second "sales Amount" column - as expected - no values are returned....


                So - How do you get this:

                Fiscal Year Month 11 Sales Month 12 Sales Month12/Month 11
                2012 $100 $150 1.50

                Without using SQL or Pivot views?
                • 5. Re: How can I use filters to show sequential quarters in a table - Heat Map
                  920386
                  as a reference - see Paragraph three of this article:

                  http://www.rittmanmead.com/2010/01/heat-maps-in-obiee/

                  It reads:
                  "Firstly, as I am looking at the difference between the a measure over two days I can not use a pivot report for this as it is not possible to calculate differences between measure columns in the pivot. But we can use the ‘FILTER’ button on the column formula editor to restrict the values returned to just those that match the filter. By doing this twice, once for each day of interest, we get two columns in the same Answers Request TABLE view that we can do the maths on. Here we need to calculate the percentage difference between the two values."

                  Is he referring to a filter based on results of another analysis or to a formula?
                  • 6. Re: How can I use filters to show sequential quarters in a table - Heat Map
                    920386
                    Got it

                    filter needed is in the "Edit Column Formula" , "Filter..." button. With a a Sales Amount column for Month 11 using the filter on "Fiscal Month Number = 11", and a second Sales Amount column with a column formula filter for "Fiscal Month Number = 12" I get one column for Month 11 and one for Month 12.

                    I can now create a column to calculate the % change

                    Resolved.