Dates

[#1]

Hi everyone. I'm very new to Qlikview and am trying to develop a dashboard using an imported (excel) calendar.

The fields on this imported claendar are:

1.Date

2.Month

3.Year

4.Sequential Month Number (starting at '1' - January 2005 - up to '252' - December 2025).

I would like to create a variable for the current month sequential number - which today (in January 2014) would be 109.

I'm sure im missing something fundamental here. I can set variables to determine current month (MONTH(TODAY()) etc - but how do I determine the sequential number? (In affect return the value of column 4. when today's date = column 1).

Any help would be very much aprreciated.

Thanks

Chris

[#2]

Hi,

A way to do it from sequential month number:

=(Mod([Sequential Month Number]-1,12)+1)*100 + div([Sequential Month Number],12)

Hope it helps.

[maxgro]

(year(Date) - year(makedate(2005))) * 12 + num(Month(Date))  as SequentialMonth

[#4]

Thanks for getting back to me Pierre.

I'm not sure what you have here. Is that the syntax for a variable? it doesnt return anything.

Apologies if Im missing the obvious.

In excel terms I would simply do a formula like:

=VLOOKUP(TODAY(),A:D,4,FALSE)

[#5]

In the Load Script Massimo?

thanks

Chris

[ashwanin]

Hi Chris,

Might be i have not get the exact requirement of yours,

But if year, Month and Date is linked in your calander then you can link all these.

There is a document uploaded in community, you can refer the same also

QlikView Design Blog

[#7]

OK, didn't get it, I was converting sequential numbers 1-252 to 100-1220

For converting current month to 1-252, Massimo's formula is fine.

For converting current month to 100-1220, it can be:

Num(Month(Today()))*100 + Year(Today()) - 2005

[maxgro · Accepted solution]

yes but it works also as expression if you have Date as dimension

[#9]

Thanks everyone. Really appreciate your help.

Chris