How to show the latest "Q2" Quartervalue

[Gyana]

Hi All,
   I have urgent requirement that "

I have Location,Fiscal_Year, Fiscal_Qtr and Headcount ( measure ). If there is no value for a Fiscal_Qtr in the Location then it will show the latest Heacount of Location where Fiscal_Qtr='Q2'

Location    Fiscal Year        Fiscal_Qtr            Amount
------------------------------------------------------------------
CD               2009              Q1-2009                10
CD               2009              Q2-2009                30
CD               2009              Q3-2009                20
CD               2009              Q4-2009                15
AB               2010               Q1-2010                32
AB               2010               Q2-2010                45
AB               2010               Q3-2010                NULL--------------------- Here I have to show 45 as 2010 FY Q2 value is 45
AB               2010               Q4-2010                10
XZ               2011               Q1-2011                50
XZ               2011               Q2-2011               NULL----------------------Here I have to show 45 as 2011 there is no Q2 so It take take the
                                                                                                           Previous year 2010 Q2 value ( i.e 45)


Please suggest me how to do that in the report.

Thanks in advance

[MMcBride]

I cannot answer your question

However, you may want to check your logic.

Your example is going to be impossible to calculate with the information you have given.

If Quarter Amount is Null then - use last Quarter or use last years quarter?

When do you use Last Quarter?
When do you use Last Years Quarter?

If this were my Data Mart I would encourage the ETL team loading the data to fix it during the load - with a tool that handles complex transformations easily...

That may not be an option but would be the safest way to handle this.

[Gyana]

Thanks MMcBride.

But my requirement is to show the latest 'Q2' data in null field.
Point 1 :
If We have 'Q2' value for a year & no value for other quarter then it will replace the 'Q2' value in null field.

Point 2:
If we don't have quarter 'Q2' value for a  year then It will take the previous 'Q2' value and replace it in the null field.

[HalfBloodPrince]

use coalesce function

[Gyana]

Thanks HalfBloodPrince

Can you please elaborate this with my Query Item ?