14 Replies Latest reply: Jan 20, 2010 11:32 AM by Michael Steedle

# Last 12 months

Hallo everybody,

I'm trying to show on the graph last 12 months (only) and till now I tried to work with calculating dimension. As it didn't work I thought to add new position (last12Months) in script. Could anybody help? In attachment my Calendar;-)

Beata Jablonska

• ###### Last 12 months

Hi,

i suggest you to use "set analysis" in expression. I attached qv application with this expression in it.

• ###### Last 12 months

Dear Milda,

Could you send it as an expression?

I'm grateful,

Beata

• ###### Last 12 months

My point is, that I could't use set analysis (at least in the way I know it). I'm calculating expression by Sum (invoiceAmount) and aggregate it 12 steps back (so I can show Moving Annual Total). When I want to show recent 12 months, the graph is useless, as it starts to calculate expression from the first shown month (instead of keeping old aggregation) I tried few things, and nothing helps, so I though script could be an idea.

Above the graph I need:

• ###### Last 12 months

sum(
{\$<
CalendarDate={'*'},
CalendarDay={'*'},
CalendarMonthAndYear={'*'},
CalendarQuarter={'*'},
CalendarWeek={'*'},
CalendarWeekAndYear={'*'},
CurYTDFlag={'*'},
WeekDay={'*'},
CalendarYear={'*'},
CalendarMonth={'*'},
CalendarDate={\$(='">' & ADDMONTHS(max(CalendarDate),-12)&'<=' & max(CalendarDate) &'"' )}
>}
1)

Here i used sum() function as an example. You can use any other function instead.

• ###### Last 12 months

Dear Milda,

Firstly thank you for your effort. Secondly, unfortunately it doesn't worl:( I have error:

Error in expression:
{ is not a valid function

MasterCalendar:
CalendarDate={(internal error)}>}1,

I put expression as following:

MasterCalendar:
TempDate AS CalendarDate,
Day(TempDate) AS CalendarDay,
Week(TempDate) AS CalendarWeek,
Weekday(TempDate) AS WeekDay,
Month(TempDate) AS CalendarMonth,
Year(TempDate) AS CalendarYear,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear,
Week (TempDate) & '-'& Year (TempDate) as CalendarWeekAndYear,
Year2Date(TempDate, 0, 1, \$(vToday))*-1 AS CurYTDFlag,
CalendarDate={\$(='">' & ADDMONTHS(max(CalendarDate),-12)&'<=' & max(CalendarDate) &'"' )}>}1)

I hope you can help me,

Greetings,

Beata

• ###### Last 12 months

So you need that, for example, at column "1-1-2010" would be the sum of "1-1-2009.....1-1-2010" periods?

• ###### Last 12 months

Yes, at column `1/1/2010` I should have a sum for last 12 months so, from February 2009:-)

• ###### Last 12 months

One solution is generating a rolling months table to associate to your calendar table. Please see the attached.

One note: I changed how you were generating CalendarMonthAndYear so it could be sorted numerically.

• ###### Last 12 months

Dear Michael,

Thank very much for this file! The only thing is, that now maximum date is December 2010 (insted of current- January), so it's not exactly what I wanted. I am trying to find why maximum date is December 2010, and when it will be changed it will be perfect! If you see the mistake, please let me know.

Acha, and I would like to ask you for one more thing- could you next time copy/paste changes you made, insted of attaching a file? Unfortunately I'm still Personal Edition user:(

Best greetings,

Beata

• ###### Last 12 months

It was built assuming that the calendar ran through today instead of the end of the current year. To make it work based on today, add the following clause to the AllMonths load (row 38 in the script):

WHERE CalendarMonthAndYear <= \$(vDateToday)

Also, I didn't make it clear before, but all you would need to do to build the chart is use Rolling Month as the dimension and sum(InvoiceAmount) as the expression.

• ###### Last 12 months

It's amazing Michael! Moving annual total works perfectly! I changed AllMonths load and it works fine but still MasterCalendar shows date till December 2010. As a reasult all my max expressions have to be changed. I tried to make it alone it script, but my abilities are still too low. Could you help one more time?

Thank you very much,

Beata

• ###### Last 12 months

I used the Master Calendar table from the original QVW you attached, which also goes through the end of 2010. Look at your LOAD...WHILE statement for TempCalendar, and you should be able to see why it generates rows through the end of the year.

• ###### Last 12 months

Good morning Beata,

now you should be able to open Michaels file.

Best wishes

Rainer

• ###### Last 12 months

Thank you Rainer!

Have a great day,

Beata