Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
yes but it works also as expression if you have Date as dimension
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.
(year(Date) - year(makedate(2005))) * 12 + num(Month(Date)) as SequentialMonth
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)
In the Load Script Massimo?
thanks
Chris
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
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
yes but it works also as expression if you have Date as dimension
Thanks everyone. Really appreciate your help.
Chris