rolling 12 months

[#1]

Hello

Could someone please give me a beginners guide to creating an expression that will give me a rolling 12 months (or last 12 months) from the current month.

I've searched on the forum and found this

count

({1<DateRaised={">=$(=addmonths(monthend(max(DateRaised))+1,-12)) <=$(=max(DateRaised))"}StatusFlagID

)

but which works as far as selecting the current month and showing the months previous, but it doesn't stop at 12, instead I get results back from day dot!

Has it got something to do with the monthend?

Any help greatly appreciated

Thanks

[Gysbert_Wassenaar]

You question is hard to answer without being able to look at the document you're working on. I've attached an example that shows a rolling 12 sum and I've tried to explain how it works.

One thing that should be mentioned is that with set analysis expressions you can only show the rolling12 for one selected month (actually the max month of the selection). If you want to use a month as dimension and show the rolling12 for every month then you need another solution, what some of us call an AsOf table.

[#3]

Thanks, that seems all a bit too advanced for my level

Anyway, I've added a file.

Basically when I select a month like March 2013 I want the chart to show just the last 12 months, not everything like it current is.

Thanks

[#4 · Accepted solution]

Hi Melanie,

your expression is working quite well.

BUT the field "DateRaised" is a timestamp. So you need to build a timestamp to compare with it. As a quick workaround I added " 00:00:00" to your expression (Take care of the leading blank). Since then it works like you expect it.

May be better: use the DateRaised as a real Date-Field, this means to convert it from timestamp to date during the load. (similar to this: Date(Floor(Input)) AS DateRaised,

count({1<DateRaised={">=$(=addmonths(monthend(max(DateRaised))+1,-12)) 00:00:00 <=$(=max(DateRaised))"},StatusFlagID={'*1*'}, IncidentType={'*Accident*'}, ListTitle={'*Employee*'}>}StatusFlagID)

HtH

Roland

[#5]

Thats great thanks

Is my whole expression a bit of a work around?

I'm happy that is works for now, but I would like to do this properly at some point.

Thanks

[#6]

Roland

Are you able to expand on this

May be better: use the DateRaised as a real Date-Field, this means to convert it from timestamp to date during the load. (similar to this: Date(Floor(Input)) AS DateRaised

as I'm unable to get my chart to sort by the date.

Thanks

[#7]

Hi Melanie,

your expression is ok. The problem is that "DateRaised" looks like a date field but is a timestamp which means a date including the time. So it will behave like a timestamp and for ex. you will have trouble in sorting it as a date.

So, if you are able to load your data for yourself, transform the incoming source values from timestamp to date before storing it in QV. Function see my post above. Otherwise ask the responsible poeple to do this for you. 

RR