Wrong month value (00)

[gadwinjer]

Hello there,

I would like to take the highest and lowest date from a table then make an other table witch contains each month between the min and the max date values.

It looks like this :

Tempotable:

Load

    min(MonthOfYear) as tempMinDate, 

    max([Last Status Date]) as tempMaxDate

resident Extract; 

LET dateIterative = date(peek('tempMinDate') - day(peek('tempMinDate')) +1, 'dd-mm-yyyy');

LET maxDate = date(peek('tempMaxDate')- day(peek('tempMaxDate')) +1, 'dd-mm-yyyy');

Drop table Tempotable;

LET currentMonth = month(dateIterative);

LET currentYear = year(dateIterative);

Calendar:

load *

inline [

MonthOfYear, Month, Year

$(dateIterative), $(currentMonth),$(currentYear)

];

do while dateIterative <= maxDate

dateIterative = AddMonths(dateIterative,1);

currentMonth = month(dateIterative);

currentYear = year(dateIterative);

load *

inline [

MonthOfYear, Month, Year

$(dateIterative), $(currentMonth),$(currentYear)

];

LOOP;

Everything work well except one thing : The month value I collect from the min and the max also is "00". Then in my table "Calendar", the min value is dec 2010 instead of jan. 2011 and the max value is dec. 2015 instead of jan. 2016. (the max value is 01-00-2016 and the min is 01-00-2011)

The format of date from the resident table is like this : 05/01/2016  as dd/mm/yyyy and for the case of the max value I just import the value from an excel file without any treatment. I do some treatment for the min value but the problem is the same so I would like to know why I have this issue for the max value first.
I tried to set the date without setting the day value to 1 :
from :
LET maxDate = date(peek('tempMaxDate') - day(peek('tempMaxDate')) +1, 'dd-mm-yyyy');
to :

LET maxDate= date(peek('tempMaxDate'), 'dd-mm-yyyy');

But that wasnt the problem obviously.

I hope I am clear. Thanks for your help !

I have to say that I absolutly HATE how dates are managed in qlik sense

[sunny_talwar · Accepted solution]

mm in QlikView denotes minutes and MM is for months, try this may be:

LET dateIterative = date(peek('tempMinDate') - day(peek('tempMinDate')) +1, 'dd-MM-yyyy');

LET maxDate = date(peek('tempMaxDate')- day(peek('tempMaxDate')) +1, 'dd-MM-yyyy');

[gadwinjer]

It's embarrassing... It's not the first time I do this mistake ! Thank you and sorry for having wasted your time

[hic]

Maybe you right now hate how dates are managed, but I can assure you that there is meaning in the madness...

When you have understood how the date management is designed, you will see that it gives you an excellent control over both how dates are loaded (interpretation) and how they are displayed (formatting).

Check the following links.

The Date Function

Get the Dates Right

Why don’t my dates work?

On Format Codes for Numbers and Dates

HIC