This content has been marked as final.
Show 6 replies

1. Re: How can I use filters to show sequential quarters in a table  Heat Map
Srini VEERAVALLI Aug 6, 2012 8:53 PM (in response to 920386)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 Aug 6, 2012 9:24 PM (in response to 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 Aug 6, 2012 9:55 PM (in response to 920386)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 Aug 7, 2012 1:13 PM (in response to 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 Aug 7, 2012 1:43 PM (in response to 920386)as a reference  see Paragraph three of this article:
http://www.rittmanmead.com/2010/01/heatmapsinobiee/
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 Aug 7, 2012 1:56 PM (in response to 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.