Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Rolling months in script

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.

7 Replies

Rolling months in script

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.

Not applicable

Rolling months in script

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

Rolling months in script

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?

Not applicable

Rolling months in script

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

Rolling months in script

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...

Not applicable

Rolling months in script

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

Not applicable

Rolling months in script

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.

Community Browser