cancel
Showing results for 
Search instead for 
Did you mean: 

Generalizing YTD & QTD calculations

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Your problem is that the where clauses are cancelling each other out.

If you were to do YTD this year and YTD last year, you'd get no results because you'd be after data where year = 2010 and year = 2011, which is obviously impossible.

You need to move all your logic into the select statements using case when logic, such as:

Sum(CASE WHEN (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)) THEN Fact_Table.Actual ELSE 0 END)

This will allow you to pull objects from the same fact table for different time periods as you choose.

Regards,

Mark

Former Member
0 Kudos

Hi Mark,

Thanks for your reply.

I made the changes as per your suggestion. I am not getting the values for the YTD, instead i am getting zero values for all the periods. How to solve this issue. But my previous YTD calculation is giving me the value like as follows,

*Period level data:*
________Jan-10____Feb-10____Mar-10
Product 1___2________4_________6

But i want the YTD function to perform like,

*YTD:* 
________Jan-10____Feb-10____Mar-10
Product 1___2________6_________12

YTD calculation for individual months has to be like as follow,

Jan = Jan

Feb = Jan + Feb

Mar = Jan + Feb + Mar...

How to do it?

Regards, Sujeev

Former Member

Answers (0)