Creating time dimensions / measures

[chappo]

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

[MFGF]

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.

[Feanor]

ceiling(([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.. 

[MFGF]

Oops - sorry, you're absolutely correct.  I must have been standing on my head when I wrote this!

MF.