Issue with calculated dimension...(Last 12 Months)

[#1]

Hi there,

I need to work in a bar chart with a calculated dimension expressing (in months) the last 12 months from the selected date. In my script I'm only using the functions "Year", "Month" and "Date" over my date field in the database and I use 3 list boxes in the application, one for year, another for months, and the last one for days, to query the transactions in my database. I know the calculated expression for the last 12 months has been a popular issue around the forums, but so far, I've been unable to find and answer that fits my particular situation. I don't have any problem with the expression, which is a simple sum of values over a particular month, but I'm having trouble to get the last 12 months over my dimension axis.

Any ideas...?

Thanks in advance

[#2]

Hi,

My understanding on your problem is, you want to show last 12 month from the selected date. if this is the case, you can do this in expression it self.

Dimension :

Year, Month, Date

Your expression would be as follow

Expression :

=sum({<YearMonth= {'>=$=(MonthName(addmonths(Max(TransDate),-12)))'}>} SALESAMOUNT)

(Untested Expression)

- Sridhar

[#3]

Thanks for your answer Sridhar,

I've tried with the expression you posted, but I get an error...I just want to select a particular month, and get the values from the last 12 months prior to that particular month. It works fine when you are in the same year, but when you're in any other month and you need to go back to the last chronological year I can't see it. I want to get the information on the same chart instead of looking at two different charts (each for one particular year)...

[#4 · Accepted solution]

Hi,

Check the attached application.

Your code should be as follow

=sum({1<Date = {'>=$(=(addmonths(max(Date),-11)))<=$(=(max(Date)))'}}>} Sale)

Apologies for my last untested post [:'(]

Hope this will help you

-Sridhar

[#5]

Thanks again Sridhar,

Could you post your testcom.xls file as well to load your tables and better understand your solution?

[#6]

Pl find the attachment

-Sridhar

[#7]

Thanks a lot Sridhar, iworked like a charm, thanks again for your patience

[#8]

Most Welcome

- Sridhar

[Anonymous]

Sridhar,

I have a similar problem, but I need a line graph.

Dimensions: year and month

expression: sum of the incidents of last 12 month dividide by the net lengh of the month.

for example:

if its nov/2010 the expression should be:

sum of incidents from dez/2009 to nov/2010 divide by the lengh of the net at nov/2010

Could you help me?

thanks a lot.

regards,

Leandra [=)