Moving annual total

[#1]

Dear QlikView users,

It's essential for my analysis to show Moving annual total (by every month, in percentage). I'm thinking, thinking and it seems not to work. In the dimension I put CalendarMonth, and then I want to use expression that will allow me to show changes so for ex.

- for January 2010 you see sum of sales for last 12 months- so from December 2008;

- for February 2010 bar you see sales for last 12 months- so from January 2009 and so on. I'm trying to use expression:

Sum(InvoiceAmount)+Sum({<(CalendarMonth,-12)>} InvoiceAmount), but of course it doesn't work.

Could anyone help me? Any ideas?

Thank you in advance,

Best regards,

Beata J.

[#2]

Hi Beata,

see the attached example.

Hope you get some good ideas.

Good luck!

Rainer

[#3]

Ach I have only personal version so I cannot open it;( Would you like to share with me what's inside?

Thank you in advance,

Beata

[#4]

Good morning Beata,

please try it again. Now you should be able to open the document.

Good luck!

Rainer

[#5]

Good morning Rainer,

Thank you very much for your effort. Right now I am working with it, and after some modifications it could be something I always wanted! I still have a question:

Do you know maybe which formula I can use, so that calculated dimension will be showing last 12 months ex. From February 2009 to January 2010?

I am trying with formula based on you file:

= CalendarDate={">=$(=addmonths(monthstart(max(CalendarDate)),-12)) <=$(=addmonths(monthend(max(CalendarDate)),0))"}>}

but it doesn't work. Any idea?

Thank you in advance,

Best greetings,

Beata

[#6]

Hi Beata,

are you able to upload a QV file I can work with?

Rainer

[Miguel_Angel_Baeyens]

Hello,

This should work

{< CalendarDate = {">=$(=addmonths(monthstart(max(CalendarDate)),-12))<=$(=(monthend(max(CalendarDate))))"} >}

You needn't use AddMonths to last date, becase, actually, you are not adding months.

[#8]

Dear QlikView users,

Anyone knows how to put calculated dimension for last 12 months?

Best regards,

Beata

P.S. Rainer it's great I can open your files!

[#9 · Accepted solution]

Hi Beata,

see the modified example worksheet RangeSum.

Hope you are a step closer to the result you are looking for.

Good luck!

Rainer

[#10]

Beata,

I´m not sure if we will have the license thing again. That´s the reason to place the file again.

Rainer