topic Moving annual total in QlikView

Moving annual total

Fri, 15 Jan 2010 15:56:19 GMT

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.

Moving annual total

Fri, 15 Jan 2010 16:10:36 GMT

Hi Beata,

see the attached example.

Hope you get some good ideas.

Good luck!

Rainer

Moving annual total

Fri, 15 Jan 2010 16:33:42 GMT

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

Moving annual total

Mon, 18 Jan 2010 09:18:54 GMT

Good morning Beata,

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

Good luck!

Rainer

Moving annual total

Mon, 18 Jan 2010 10:55:04 GMT

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

Moving annual total

Mon, 18 Jan 2010 14:17:44 GMT

Hi Beata,

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

Rainer

Moving annual total

Miguel_Angel_Baeyens — Mon, 18 Jan 2010 14:30:29 GMT

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

Mon, 18 Jan 2010 17:26:26 GMT

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

Tue, 19 Jan 2010 09:49:57 GMT

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

Tue, 19 Jan 2010 09:58:15 GMT

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

Tue, 19 Jan 2010 10:02:02 GMT

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

Tue, 19 Jan 2010 13:31:59 GMT

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:
Load
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');

Best wishes

Rainer