Last 12 months

[#1]

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;-)

Thnak you in advance,

Beata Jablonska

[#2]

Hi,

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

I hope this will help you 🙂

[#3]

Dear Milda,

Could you send it as an expression?

I'm grateful,

Beata

[#4]

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:

[#5]

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.

[#6]

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

[#7]

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:
LOAD
CalendarDate={(internal error)}>}1,

I put expression as following:

MasterCalendar:
LOAD
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

[#8]

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

[msteedle]

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.

[#10]

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:(

Thank you in advance,

Best greetings,

Beata