help with last 15 quarters data

[pcog]

Hi guys,

I have a req where I need to show the last 15 quarters data on a report.
I know we can get the quarters by getting months (Jan, Feb, Mar) as Q1 and so on, but not sure how to get the last 15 quarters data.

Can someone shed light on me.
Thanks in advance.

[MFGF]

Are you using a dimensional or relational package?

MF.


Sent from my iPad using Tapatalk HD

[pcog]

Hello MF,

I'm using the relational package. also please note that unfornately we don't have a real Time dimension so, I have to use a one Date column for it :(

Thanks

[pcog]

hey MF,

was just checking...do you have any solution for this?

[navissar]

This should be fun. Basically, deriving dates is just playing with numbers.
So let's suppose I want to get the last 15 quarters (Does your client realise he's asking for almost 4 years?), and there's no proper time dimension (Why?). We start with what we know: Today's date. That is current_date, obviously. We'll start by getting the month of today's date, by putting in extract(month, current_date). 
So now we have 12. good. Which quarter is 12? you could put in a long case statement to find that out (case extract(month, current_date) when 1 then 1 when 2 then 1 when 3 then 1 when 4 then 2...), but that's just horrible isn't it? I'd use the following method:
ceiling(extract(month, current_date)/3)
So now we have the current quarter. We want to go back 15 quarters, which means we want to start on the first day of the first month of the quarter 15 quarters ago. Since we don't have a proper time dimension, we can't just subtract quarters. Also, we can't subtract days from current date because quarters differ in length. What all quarters have in common, though, is that they hold 3 months. So, 15 quarters will always be 45 months. So, if we subtract 45 months from the first month of this quarter, we will get the first month of the quarter 15 quarters ago. So, next we need to find the first month of current quarter. Again, we could be using some horrible case statement, but we don't have to because we can math.
The formula for getting the first month of the quarter from the quarter number is Q*2+(Q-2).
If Q=1 the formula will be 1*2+(1-2)=2-1=1 ->January.
If Q=2 the formula will be 2*2+(2-2)=4+0=4 ->April.
If Q=3 the formula will be 3*2+(3-2)=6+1=7 ->July.
If Q=4 the formula will be 4*2+(4-2)=8+2=10 ->October.

So, now our data item looks like this:

Code Select Expand

ceiling(extract(month, current_date)/3)*2+(ceiling(extract(month, current_date)/3)-2)
next step is technical - we'll turn this to the date of the first day of the current quarter like this:

Code Select Expand

_make_timestamp(extract(year,current_date),
ceiling(extract(month, current_date)/3)*2+(ceiling(extract(month, current_date)/3)-2),1)
This should return October 1st 2014 for now.
Now we go back 45 months:

Code Select Expand

_add_months (_make_timestamp(extract(year,current_date),
ceiling(extract(month, current_date)/3)*2+(ceiling(extract(month, current_date)/3)-2),1),-45)
This should give us the date of the first day of the quarter 15 quarters ago (January 1st 2011 if we're looking right now).
Now you can create a filter with

Code Select Expand


[date] between _add_months (_make_timestamp(extract(year,current_date),
ceiling(extract(month, current_date)/3)*2+(ceiling(extract(month, current_date)/3)-2),1),-45) and current_date
(I'm assuming you don't have future data. If you do and you need to go until the end of the quarter rather than current date, you'll need to apply some more logic to find last day of quarter).

Good luck!

[pcog]

Nirmod,

Thanks a lot for your reply.
I did something similar...
First I created the below data item for the last day of the last quarter (if we are in the middle of the quarter like today, we need to report till the last quarter)

Code Select Expand

case
when extract(month,current_date) in (1,4,7,10) then _last_of_month (_add_months (current_date,-1))
when extract(month,current_date) in (2,5,8,11) then _last_of_month (_add_months (current_date,-2))
when extract(month,current_date) in (3,6,9,12) then _last_of_month (_add_months (current_date,-3))
end

then I filtered the data

Code Select Expand

_add_months([Last_day_Prior_Quarter], -45)

But, unfortunatly this is for default...user should also have an option to select Month and Year to run the report.
Any help on this?