Rolling months in script

[#1]

Dear QlikView users,

I have a problem with Rolling Months option in my script. Thanks to Michael Steedle I could use it in one file, but it seems that it doesn't work in my new model. My Main Calendar looks like that:

LET vDateMin = Num(MakeDate(2008,1,1));
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 1)));
LET vDateToday = Num(Today());

TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo()-1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

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,
Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag,
YearToDate(TempDate, 0, Ceil(Month(Today())/3) * 3 - 2 ) as CurQTDFlag,
YearToDate(TempDate, -1, Ceil(Month(Today())/3) * 3 - 2 ) as LastQTDFlag,
Year2Date(TempDate, 0, Month(Today())) as CurMTDFlag,
Year2Date(TempDate, -1, Month(Today())) as LastMTDFlag

RESIDENT TempCalendar
ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

Could anyone help?

Thank you in advance,

Beata Jablonska

P.S. I would forget! Does anyone have an idea why values for my CurMTDFlag are negative(ex. sum of sales turns -60.00 Euro, instead of 60.000 Euro)? CurYTDFlag works correctly.

[Miguel_Angel_Baeyens]

Hello Beata,

To your MasterCalendar, I would try with functions InQuarterToDate and InMonthToDate as flags to know your rolling dates. Take into account that the result of all these year to date functions is "true" or "false". QlikView speaking, true equals to "-1" and false equals to "0", so you should not add to your CurYTD and LastYTD Flags, instead of setting them in the set analysis

Sum({< LastYTDFlag = {'-1'} >} Sales)

will sum sales for the last year rolling period.

[#3]

Dear Miguel,

Now I understand why my values where on negative, thanks! Another question- any idea how I can make Rolling Months option? I would like to ahow on bar chart Moving Annual Total (ex. for February 2010 there will be shown sum of last 12 months sales etc).

Thank you in advance,

Best greetings,

Beata Jablonska

[Miguel_Angel_Baeyens]

Hello Beata,

As of today, February 2, with

{< CalendarDate = {'>=$(=AddMonths(Today(), -12))<=$(=Date(Today()))'} >}

as set analysis, will sum from Feb 2 2009 to Feb 2 2010. Is that what you are looking for?

[#5]

Dear Miguel,

Yes, it sound like that what I need. Right now I am achieving MAT by usage of simple accumulaqtion (12 steps back), but it has disadvantage of showing all of the months in your system (also those that cannot accumalute 12 steps back) and with this I cannot count % MAT (unless you maybe know how to check the difference between bars, current and from previous month?). Below you can see my MAT (at least smth like MAT)

When I tried to use your expression in bart chart, it says 'no data to display'. I faced this problem with some other functions earlier, that's why I though the most convinient would be to use RollingMonths in script.

Thank you for your help,

Beata

[Miguel_Angel_Baeyens]

Hello Beata,

Having Month and Year as dimensions and sum of values as expression, your should modify the above set analysis changing "CalendarDate" for your master fact date (i.e.: InvoiceDate). You should get results provided you have your master calendar working properly and invoices which dates go from today minus12 months to today.

Or I may be missing something...

[#7]

Dear Miguel,

Hmmmm yes, there has to be smth wrong, cause now I tried also:

sum({<CalendarDate={">$(=addmonths(Date,-12)) <=$(=Date)"}>} InvoiceAmount)

and still it says "no data to display". The dates of my invoices are linked to Master Calendar, so I have date field "CalendarDate" only. Maybe that's the problem?

Anyway, I see, that maybe I didn't explain my problem properly. I want to show last 12 months, but each bar should sum previous 12 months. To show only 12 months, I use:

sum(if(CalendarDate>addmonths(today(),-12),Sales))

and first requirment is fullfilled (it's only 12 months on graph), but now I want each bar to show sum of sales for last 12 months (so called Moving Annual Total) and here I'm stucked...

Thank you for your help and patience,

Greetings,

Beata

[#8]

Did you find a solution for this problem?

I'm asking because I'm stuck at the same problem at the moment. Any help would be appreciated.

Thanks.