hello,

can someone please guide me how to write an expression for quarter for previous month.
like for current quarter, the expression is

=year(now()) & "Q" & DatePart(DateInterval.Quarter,now())

as i want it to be '2009Q1'.

i have 8 columns in my report, and i have 1 parameter whose value is '2009Q1' which is the current month quarter.
so how do i get expressions for 7 previous months?

say, for previous 1 quarter, i havta get '2008Q4' and likewise.

the report runs by current date. i hope i am clear. if not, please let me know.

Ok, that's what I thought. You just want the label to read those, right. Unless you want to write a custom code, I suggest just use static approach. So, for each previous qtr, the expression would be:

=now().AddMonths(-3).Year & "Q" & DatePart("q",now().AddMonths(-3))
=now().AddMonths(-6).Year & "Q" & DatePart("q",now().AddMonths(-6))
=now().AddMonths(-9).Year & "Q" & DatePart("q",now().AddMonths(-9))
=now().AddYears(-1).Year & "Q" & DatePart("q",now().AddYears(-1))
=now().AddYears(-1).AddMonths(-3).Year & "Q" & DatePart("q",now().AddYears(-1).AddMonths(-3))
=now().AddYears(-1).AddMonths(-6).Year & "Q" & DatePart("q",now().AddYears(-1).AddMonths(-6))

---

Chicagoan ...

Hi,

I guess I'm a little bit loss as far as you want the last 7 previous months or 7 previous quarters. Anyhow, to give you an idea, to get the previous quarter, your expression would be like this:

=now().AddMonths(-3).Year & "Q" & DatePart("q",now().AddMonths(-3))

So, if you want the last 7 months and which quarters they are, it would be something like this:
=now().AddMonths(-1).Year & "Q" & DatePart("q",now().AddMonths(-1))
=now().AddMonths(-2).Year & "Q" & DatePart("q",now().AddMonths(-2))
=now().AddMonths(-3).Year & "Q" & DatePart("q",now().AddMonths(-3))
... and so forth.

I hope you got the idea. Good luck.

---

Chicagoan ...

I'm actually looking for previous 7 'quarters' not 'months'.

like, current quarter is '2009Q1', so the other column would be '2008Q4', prior to that it will be '2008Q3'. how do i get it?

=now().AddMonths(-4).Year & "Q" & DatePart("q",now().AddMonths(-4))
=now().AddMonths(-7).Year & "Q" & DatePart("q",now().AddMonths(-7))
=now().AddMonths(-10).Year & "Q" & DatePart("q",now().AddMonths(-10))

Ok, that's what I thought. You just want the label to read those, right. Unless you want to write a custom code, I suggest just use static approach. So, for each previous qtr, the expression would be:

=now().AddMonths(-3).Year & "Q" & DatePart("q",now().AddMonths(-3))
=now().AddMonths(-6).Year & "Q" & DatePart("q",now().AddMonths(-6))
=now().AddMonths(-9).Year & "Q" & DatePart("q",now().AddMonths(-9))
=now().AddYears(-1).Year & "Q" & DatePart("q",now().AddYears(-1))
=now().AddYears(-1).AddMonths(-3).Year & "Q" & DatePart("q",now().AddYears(-1).AddMonths(-3))
=now().AddYears(-1).AddMonths(-6).Year & "Q" & DatePart("q",now().AddYears(-1).AddMonths(-6))

---

Chicagoan ...

thanks Isham. i like the -3, -6, -9, -12, -15, -18, -21 idea better. Thank you.