Creating a sum for each quater

[aftabp]

Look at the following code

Code Select Expand


if ([year]  =
( CASE when ?P_year? = 0 then extract(year; current_date) else
?P_year? end )
)

then(

CASE when
[Presentation Layer].[Dim Time].[Dim Month] = ( CASE when ?P_Month? = 0 then
extract(month; current_date)
WHEN ?P_Month? = 21 THEN 3
WHEN ?P_Month? = 22 THEN 6
WHEN ?P_Month? = 23 THEN 9
WHEN ?P_Month? = 24 THEN 12
else  ?P_Month?
end)

then
(if (?pThousand?=1)
then ([Presentation Layer].[Budget book].[Budget]
)
else ([Presentation Layer].[Budget book].[Budget]/1000 )
)
end
)
else (0)


In the above code 21, 22, 23 and 24 represents Q1, Q2, Q3 and Q4

When use the above code the Presentaion Layer returns data for month 3, 6, 9 and 12 and not for the Quarters as it is suppose to.

How do i modify the above code to get the budget for the quarters. The budget has both positive and negative numbers.

[gpollock]

It looks like your nested case statements are jumbling, and you forgot to end your inner case for the month.  I'm reading it over and over and trying to understand the plain english of it.  I recommend creating a data item in your query for month to avoid the confusion of the nested values (you can always remove it after you get the code working).  Then are you trying to look for a range?  Month 21 should sum months 1-3?

[aftabp]

yes gpollock,
Month 21 should sum months 1-3
Month 22 should sum months 4-6
Month 23 should sum months 7-9
Month 24 should sum months 10-12

[gpollock]

Okay, I have three recommendations for how to handle.  I'm not able to see your data, so I can't be more specific in my recommendations.

1) In your query, create data items for each step of the process you're trying to do.  As you're happy that the data item returns what you think, move to the next data item.  It will make it easier to see what you're doing, and you can always go back to one complicated data item later.
2) You said you have quarter saved as a string on your time dimension?  Use string functions to parse the quarter number and filter on that.  Cognos has a substring function that is 1-based.
3) Instead of having one prompt for month that includes quarters, have two optional prompts: one for month and one for quarter.  You'll see the biggest performance benefit here, and the prompt page will be less confusing for the end user.