Hiya all,
i have a denormalised data table (in SQL Server) which the company i am working for will use in a Cognos 8 Framework manager application. I have a couple of questions i was wondering if you could help me with
1. i have a standard date - 01/01/2001 (eg) i want to create a heirarchical view of this data, (days / months / qtrs / years) - i have managed to create the days / months / year, but i am struggling with a quarter - can anyone suggest a method of creating these rollups
2. I have 3 standard measures - sales / qty / cost. These have all been placed in a default 'measures' dimension. I want to create additional analysis of avg selling price / avg cost price and margin. I have been able to create the calculations for these, but when i assign them as a measure they go into their own measures folder, as opposed to the default measures bucket. Is there a way of getting all the measures into the one measures folder?
3. I need another 'measures' calc for the following
Quarter to date, previous quarter, Year to date , prior year quarter, prior ytd
As you can probably tell - i am very new to the world of framework manager (come back transformer all is forgiven - so you help would be appreciated
Hi,
Assuming you want quarters based on Calendar Year (ie Q1=Jan,Feb,Mar Q2=Apr,May,Jun etc) and that you have already extracted the month number, you could calculate your quarter number as follows:
ceiling(([Your Month Item]-1)/3) + 1
Otherwise, if you quarters started in a different month, you would probably use an if/then/else calculation:
if ([Your Month Item] >=4 and [Your Month Item] <=6) then (1) else if ([Your Month Item] >=7 and [Your Month Item] <=9) then (2) else if ([Your Month Item] >=10 and [Your Month Item] <=12) then (3) else (1)
The above assumes Q1 starts in April.
Once you have your quarter number, you can combine it with your year to make a calendar quarter as follows:
[Your Year Item] * 10 + [Your Quarter Item]
This would give you 20064 for Q4 of 2006
Regards,
MF.
Quoteceiling(([Your Month Item]-1)/3) + 1
I think there is a typo in this expression. Function [ceiling] does not provide the correct result. The opposite function [floor] does.
Still this post was pretty usefull to me.. ;)
Oops - sorry, you're absolutely correct. I must have been standing on my head when I wrote this! ;D
MF.