on 01-27-2011 11:17 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
Have a look at this blog entry:
Regards,
Mark
User | Count |
---|---|
94 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.