11 Replies Latest reply: Jan 19, 2010 8:31 AM by Rainer Filoda

# Moving annual total

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?

Best regards,

Beata J.

• ###### Moving annual total

Hi Beata,

see the attached example.

Hope you get some good ideas.

Good luck!

Rainer

• ###### Moving annual total

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

Beata

• ###### Moving annual total

Good morning Beata,

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

Good luck!

Rainer

• ###### Moving annual total

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:

but it doesn't work. Any idea?

Best greetings,

Beata

• ###### Moving annual total

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.

• ###### Moving annual total

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!

• ###### Moving annual total

Hi Beata,

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

Rainer

• ###### Moving annual total

Hi Beata,

see the modified example worksheet RangeSum.

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

Good luck!

Rainer

• ###### Moving annual total

Beata,

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

Rainer

• ###### Moving annual total

Rainer it's great!

I will remeber this function, it can be really useful. One more question- maybe you know how to show last 12 months only? I was tryinh with calculated dimension, it didn't work, but maybe it's possible to add smth like Last12Months in script?

Thank you very much for you effort,

Beata

• ###### Moving annual total

Hello again,

here are an example for calculating the avarage for last 100 days. Maybe this could be an option for you.

Let v100 = 0;

For v100 = 0 to 99

Linie_100_Tage_Neu:
Date(Peek('DaxDatum',\$(v100),'Dax'),'DD.M.YYYY') as DaxDatum,
Date(Peek('DaxDatum',\$(v100),'Dax'),'DD.M.YYYY') as DaxDatum_100,
Avg(Punkte) as Linie_100_Tage_Neu
resident Dax
where DaxDatum >= Peek('DaxDatum',99 + \$(v100),'Dax')
and DaxDatum <= Peek('DaxDatum',\$(v100),'Dax');

next

Best wishes

Rainer