topic Re: Dates in QlikView

Dates

Thu, 30 Jan 2014 13:19:50 GMT

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

Re: Dates

Thu, 30 Jan 2014 13:26:30 GMT

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.

Re: Dates

maxgro — Thu, 30 Jan 2014 13:32:57 GMT

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

Re: Dates

Thu, 30 Jan 2014 13:34:13 GMT

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)

Re: Dates

Thu, 30 Jan 2014 13:35:26 GMT

In the Load Script Massimo?

thanks

Chris

Re: Dates

ashwanin — Thu, 30 Jan 2014 13:38:18 GMT

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

Re: Dates

Thu, 30 Jan 2014 13:40:16 GMT

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

Re: Dates

maxgro — Thu, 30 Jan 2014 13:40:36 GMT

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

Re: Dates

Thu, 30 Jan 2014 13:43:37 GMT

Thanks everyone. Really appreciate your help.

Chris