Skip to Content
0
Former Member
Jan 27, 2011 at 11:17 AM

Generalizing YTD & QTD calculations

127 Views

Hi,

I am having month as leaf level in the time table. There are 14 months each month is having 26 days in the time table.

Time table format:

TIME_KEY	PERIOD_NAME	   START_DATE	END_DATE	YEAR_START_DATE	QUARTER_START_DATE PERIOD_YEAR 	PERIOD_NUM	QUARTER_NUM
200701	JAN-07	12/30/2006	1/26/2007	12/30/2006	12/30/2006	2007	1	1
200702	FEB-07	1/27/2007	2/23/2007	12/30/2006	12/30/2006	2007	2	1
200703	MAR-07	2/24/2007	3/30/2007	12/30/2006	12/30/2006	2007	3	1
200704	APR-07	3/31/2007	4/27/2007	12/30/2006	3/31/2007	2007	4	2
200705	MAY-07	4/28/2007	5/25/2007	12/30/2006	3/31/2007	2007	5	2
200706	JUN-07	5/26/2007	6/29/2007	12/30/2006	3/31/2007	2007	6	2
200707	JUL-07	6/30/2007	7/27/2007	12/30/2006	6/30/2007	2007	7	3
200708	AUG-07	7/28/2007	8/24/2007	12/30/2006	6/30/2007	2007	8	3
200709	SEP-07	8/25/2007	9/28/2007	12/30/2006	6/30/2007	2007	9	3
200710	OCT-07	9/29/2007	10/26/2007	12/30/2006	9/29/2007	2007	10	4
200711	NOV-07	10/27/2007	11/23/2007	12/30/2006	9/29/2007	2007	11	4
200712	DEC-07	11/24/2007	12/28/2007	12/30/2006	9/29/2007	2007	12	4
200713	P13-07	12/28/2007	12/28/2007	12/30/2006	9/29/2007	2007	13	4
200714	P14-07	12/28/2007	12/28/2007	12/30/2006	9/29/2007	2007	14	4
200801	JAN-08	12/29/2007	1/25/2008	12/29/2007	12/29/2007	2008	1	1
200802	FEB-08	1/26/2008	2/22/2008	12/29/2007	12/29/2007	2008	2	1
200803	MAR-08	2/23/2008	3/28/2008	12/29/2007	12/29/2007	2008	3	1
200804	APR-08	3/29/2008	4/25/2008	12/29/2007	3/29/2008	2008	4	2
200805	MAY-08	4/26/2008	5/23/2008	12/29/2007	3/29/2008	2008	5	2
200806	JUN-08	5/24/2008	6/27/2008	12/29/2007	3/29/2008	2008	6	2
200807	JUL-08	6/28/2008	7/25/2008	12/29/2007	6/28/2008	2008	7	3
200808	AUG-08	7/26/2008	8/22/2008	12/29/2007	6/28/2008	2008	8	3
200809	SEP-08	8/23/2008	9/26/2008	12/29/2007	6/28/2008	2008	9	3
200810	OCT-08	9/27/2008	10/24/2008	12/29/2007	9/27/2008	2008	10	4
200811	NOV-08	10/25/2008	11/21/2008	12/29/2007	9/27/2008	2008	11	4
200812	DEC-08	11/22/2008	12/26/2008	12/29/2007	9/27/2008	2008	12	4
200813	P13-08	12/26/2008	12/26/2008	12/29/2007	9/27/2008	2008	13	4
200814	P14-08	12/26/2008	12/26/2008	12/29/2007	9/27/2008	2008	14	4

I am using TIME_KEY column (which is integer) in the calculations. The YTD & QTD calculations are as follows,

YTD calculation in Universe:

Select section:

Sum (Actual)

Where section:

(D_TIME.TIME_KEY 
Between	 
to_number(Concat(Substr(to_char(@Prompt('Enter End Period: ', 'A', 'Dim Time\YearPeriod', Mono, Constrained), '999999'),1,5),'01'), '999999') 
And 
@Prompt ('Enter End Period: ', 'A', 'Dim Time\YearPeriod', Mono, Constrained))

QTD calculation in Universe:

Select section:

Sum (Actual)

Where section:

(D_TIME.TIME_KEY 
Between 
to_number(Concat(Substr(to_char(@Prompt('Enter End Period: ', 'A', 'Dim Time\YearPeriod', Mono, Constrained), '999999'), 1, 5),
(Case Substr(to_char(@Prompt('Enter End Period: ', 'A', 'Dim Time\YearPeriod' ,Mono, Constrained), '999999'), 6, 7)	
		 When '01' Then '01'
		 When '02' Then '01'
		 When '03' Then '01'
		 When '04' Then '04'
		 When '05' Then '04'
		 When '06' Then '04'
		 When '07' Then '07'
		 When '08' Then '07'
		 When '09' Then '07'
		 When '10' Then '10'
		 When '11' Then '10'
		 When '12' Then '10'  
 When '13' Then '10'  
 When '14' Then '10'
	 End)), '999999') 
And 
@Prompt ('Enter End Period: ', 'A',' Dim Time\YearPeriod', Mono, Constrained))

Using the above YTD & QTD calculations, I can restrict the periods but the values are not rolling up for each month. For example,

Period level data:

________Jan-10____Feb-10____Mar-10

Product 1___2________4_________6

YTD: (the output has to come like the below one, but I am getting the same period value)

________Jan-10____Feb-10____Mar-10

Product 1___2________6_________12

Please guide me to write a generalized YTD and QTD calculation in Universe level?

Regards, Sujeev