topic Last 12 months in QlikView

Last 12 months

Tue, 19 Jan 2010 10:11:01 GMT

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

Last 12 months

Tue, 19 Jan 2010 11:06:10 GMT

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 🙂

Last 12 months

Tue, 19 Jan 2010 11:09:02 GMT

Dear Milda,

Could you send it as an expression?

I'm grateful,

Beata

Last 12 months

Tue, 19 Jan 2010 11:30:39 GMT

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:

Last 12 months

Tue, 19 Jan 2010 11:33:05 GMT

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.

Last 12 months

Tue, 19 Jan 2010 11:44:21 GMT

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

Last 12 months

Tue, 19 Jan 2010 11:48:40 GMT

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

Last 12 months

Tue, 19 Jan 2010 11:50:09 GMT

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

Last 12 months

msteedle — Tue, 19 Jan 2010 17:47:33 GMT

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.

Last 12 months

Wed, 20 Jan 2010 09:03:09 GMT

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

Last 12 months

Wed, 20 Jan 2010 09:17:38 GMT

Good morning Beata,

now you should be able to open Michaels file.

Best wishes

Rainer

Last 12 months

Wed, 20 Jan 2010 10:14:14 GMT

Thank you Rainer!

Have a great day,

Beata

Last 12 months

msteedle — Wed, 20 Jan 2010 14:33:58 GMT

It was built assuming that the calendar ran through today instead of the end of the current year. To make it work based on today, add the following clause to the AllMonths load (row 38 in the script):

WHERE CalendarMonthAndYear <= $(vDateToday)

Also, I didn't make it clear before, but all you would need to do to build the chart is use Rolling Month as the dimension and sum(InvoiceAmount) as the expression.

Last 12 months

Wed, 20 Jan 2010 16:08:05 GMT

It's amazing Michael! Moving annual total works perfectly! I changed AllMonths load and it works fine but still MasterCalendar shows date till December 2010. As a reasult all my max expressions have to be changed. I tried to make it alone it script, but my abilities are still too low. Could you help one more time?

Thank you very much,

Beata

Last 12 months

msteedle — Wed, 20 Jan 2010 16:32:11 GMT

I used the Master Calendar table from the original QVW you attached, which also goes through the end of 2010. Look at your LOAD...WHILE statement for TempCalendar, and you should be able to see why it generates rows through the end of the year.