sql for rolling/ trailing quarters
I am having difficulty customizing this sql to a poiint where the report shows trailing 4 quarters when any date is selected from the quarter dropdown (prompt), so far I have customized it as below:
Time."Fiscal Quarter" < @{quarter}{VALUEOF(NQ_SESSION."CURR_OPEN_QUART")}
AND
Time."Fiscal Quarter" > (case when substring(@{quarter}{VALUEOF(NQ_SESSION."PRIOR_YEAR_QUART")} from 6 for 8)='Q 1' then cast(cast(substring(@{quarter}{VALUEOF(NQ_SESSION."PRIOR_YEAR_QUART")} from 1 for 4) as int)-1 as char(4))||' '||'Q 4' when substring(@{quarter}{VALUEOF(NQ_SESSION."PRIOR_YEAR_QUART")} from 6 for 8)='Q 2' then substring(@{quarter}{VALUEOF(NQ_SESSION."PRIOR_YEAR_QUART")} from 1 for 4)||' '||'Q 1' when substring(@{quarter}{VALUEOF(NQ_SESSION."PRIOR_YEAR_QUART")} from 6 for 8)='Q 3' then substring(@{quarter}{VALUEOF(NQ_SESSION."PRIOR_YEAR_QUART")} from 1 for 4)||' '||'Q 2' else substring(@{quarter}{VALUEOF(NQ_SESSION."PRIOR_YEAR_QUART")} from 1 for 4)||' '||'Q 3' end)
This sql is working only per default values on the dashboard but when u select any specific quarter from the quarter prompt dropdown list, it produces errors - I am trying to display 4 trailing months eg if a person selects 2002 Q 4, the information displayed show show values from 2002 Q 1 - 2002 Q 4