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
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.
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.
use coalesce function
Thanks HalfBloodPrince
Can you please elaborate this with my Query Item ?