QTR,Prior year qtr, Prior year same priod, Rolling 3 month

building semantic layer
1

Hi All,

Could you please let me know how to calculate current qtr and previous qtr, rolling 13 month and one year prior month measures.

I have below columns in time_months dim.
MONTH_KEY
MONTH_NUM
MONTH_NAME
MONTH_NAME_ABBR
CALENDAR_YR_MONTH_NUM
BRUNS_YR_MONTH_NUM
MODEL_YR_MONTH_NUM
MONTH_NUM_OVERALL
CALENDAR_YR_QTR
BRUNS_YR_QTR
MODEL_YR_QTR
QTR_NUMBER_OVERALL
CALENDAR_YR
BRUNS_YR
MODEL_YR
YR_NUM_OVERALL

Please find the same data for time_month dimension.

Thanks in advance.
sample_date.txt (2.0 KB)

write2me (BOB member since 2007-08-12)

2

You need to relate everything to the current date. Which db are you using? The exact syntax will depend on this.

steveayres :uk: (BOB member since 2006-11-23)

3

I suggest you have a look at Dave’s articles on the subject to get you started.

wahey :netherlands: (BOB member since 2007-05-31)

4

We are working on oracle 10g

write2me (BOB member since 2007-08-12)

5

I think wahey has hit the nail on the head. Dave’s articles explain it far better than I will.

Good luck,

Steve

steveayres :uk: (BOB member since 2006-11-23)

6

My Scenario to display the data is based on other data load time.

One fact table data load is having 1 month lag data so i have to display the rest of the data based on that time only.

Please help me out in this or provide the code to start with.

Thanks!!!

write2me (BOB member since 2007-08-12)

7

Dave,

Might be my mind is not working currently.
I appreciate if you can provide me the code to calculate unit for for current quater and previous quater.

Thanks in advance.

write2me (BOB member since 2007-08-12)

8

write2me,

Try these as condition objects:

Current Quarter:

decode(
	to_number(to_char(
		trunc(SYSDATE),'Mm'))
	,'1','1','2','1','3','1','4','2','5','2','6','2','7','3','8','3','9','3','10','4','11','4','12','4','5')
 = decode(
	to_number(to_char(
		trunc(table.date),'Mm'))
	,'1','1','2','1','3','1','4','2','5','2','6','2','7','3','8','3','9','3','10','4','11','4','12','4','5')
and to_number(to_char(
	trunc(sysdate),'YYYY'))
 = to_number(to_char(
	trunc(table.date),'YYYY'))

Previous Quarter:

decode(
	CASE WHEN to_number(to_char(
		trunc(SYSDATE),'Mm'))
	-3 <= 0 then 12 else to_number(to_char(
		trunc(SYSDATE),'Mm'))
	-3 END,'1','1','2','1','3','1','4','2','5','2','6','2','7','3','8','3','9','3','10','4','11','4','12','4','5')
 = decode(
	to_number(to_char(
		trunc(table.date),'Mm'))
	,'1','1','2','1','3','1','4','2','5','2','6','2','7','3','8','3','9','3','10','4','11','4','12','4','5')
and CASE WHEN to_number(to_char(
	trunc(SYSDATE),'Mm'))
-3 <= 0 THEN to_number(to_char(
		trunc(sysdate),'YYYY'))
-1 ELSE to_number(to_char(
	trunc(sysdate),'YYYY'))
END = to_number(to_char(
	trunc(table.date),'YYYY'))

Obviously ‘table.date’ should be replaced with your date field.

steveayres :uk: (BOB member since 2006-11-23)

9

I created a condition in my universe very similar to yours: it is the last quarter with 3 months lag:
table_name.some_date BETWEEN Add_Months(TRUNC (sysdate, ‘Q’),-3) and TRUNC (sysdate, ‘Q’) - 1 / 86400 )

May be you can use that to help you…

audew (BOB member since 2007-11-08)

10

Cheers audew,

Thanks for the tip on using ‘Q’ - certainly simpler than a decode. :yesnod:

steveayres :uk: (BOB member since 2006-11-23)