Odd numbers in DATE field output

[Tanalex]

QlikView 11.2 The TMPDATE is in the master calendar script whose formate is mm/dd/yyyy.

The first IF statements works fine, grabbing the last day of last month and the first day of three months prior.  These are used to create dynamic "quarterly" labels.  For example, running the statement below produces this, which is what I was expecting (YYYYMM):

  IF((Date(Floor(tmpDate))) <= (MonthsEnd(1,Today(),-1)) AND (Date(Floor(tmpDate))) >= (MonthsStart(1,Today(),-3))

  ,Date(MonthsEnd(1,Today(),-1),'YYYYMM-' & Date(MonthsStart(1,Today(),-3),'YYYYMM'))

  ,

  IF((Date(Floor(tmpDate))) <= (MonthsEnd(1,Today(),-4)) AND (Date(Floor(tmpDate))) >= (MonthsStart(1,Today(),-6))

  ,Date(MonthsEnd(1,Today(),-4),'YYYYMM-' & Date(MonthsStart(1,Today(),-6),'YYYYMM'))

  ,

  IF((Date(Floor(tmpDate))) <= (MonthsEnd(1,Today(),-7)) AND (Date(Floor(tmpDate))) >= (MonthsStart(1,Today(),-9))

  ,Date(MonthsEnd(1,Today(),-7),'YYYYMM-' & Date(MonthsStart(1,Today(),-9),'YYYYMM'))

  ,

  IF((Date(Floor(tmpDate))) <= (MonthsEnd(1,Today(),-10)) AND (Date(Floor(tmpDate))) >= (MonthsStart(1,Today(),-12))

  ,Date(MonthsEnd(1,Today(),-10),'YYYYMM-' & Date(MonthsStart(1,Today(),-12),'YYYYMM'))

  ,''))))

  AS  [Month3Roll2],

My issues is when I change the label to try to be MMMYYYY, it works for the first and last statements, but not the second or third.  See example.  Notice the numbers in lieu of letters

  IF((Date(Floor(tmpDate))) <= (MonthsEnd(1,Today(),-1)) AND (Date(Floor(tmpDate))) >= (MonthsStart(1,Today(),-3))

  ,Date(MonthsEnd(1,Today(),-1),'MMMYYYY-' & Date(MonthsStart(1,Today(),-3),'MMMYYYY'))

  ,

  IF((Date(Floor(tmpDate))) <= (MonthsEnd(1,Today(),-4)) AND (Date(Floor(tmpDate))) >= (MonthsStart(1,Today(),-6))

  ,Date(MonthsEnd(1,Today(),-4),'MMMYYYY-' & Date(MonthsStart(1,Today(),-6),'MMMYYYY'))

  IF((Date(Floor(tmpDate))) <= (MonthsEnd(1,Today(),-7)) AND (Date(Floor(tmpDate))) >= (MonthsStart(1,Today(),-9))

  ,Date(MonthsEnd(1,Today(),-7),'MMMYYYY-' & Date(MonthsStart(1,Today(),-9),'MMMYYYY'))

  ,

  IF((Date(Floor(tmpDate))) <= (MonthsEnd(1,Today(),-10)) AND (Date(Floor(tmpDate))) >= (MonthsStart(1,Today(),-12))

  ,Date(MonthsEnd(1,Today(),-10),'MMMYYYY-' & Date(MonthsStart(1,Today(),-12),'MMMYYYY'))

  ,''))))

Any ideas?  I have tried various formatting/interpretation functions to no avail.  Thanks in advance for any all comments/suggestions.

[luismadriz]

Hi Michael, odd indeed!

What to you have for MonthNames?

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

Cheers,

Luis

[swuehl · Accepted solution]

Please try to post a working sample script, your current script does not seem to produce any results.

Just adding /  changing some parenthesis, I got the requested results:

LOAD *,

  IF( Date(Floor(tmpDate)) <= MonthsEnd(1,Today(),-1) AND Date(Floor(tmpDate)) >= MonthsStart(1,Today(),-3)

  ,Date(MonthsEnd(1,Today(),-1),'MMMYYYY-') & Date(MonthsStart(1,Today(),-3),'MMMYYYY')

  ,

  IF( Date(Floor(tmpDate)) <= MonthsEnd(1,Today(),-4) AND Date(Floor(tmpDate)) >= MonthsStart(1,Today(),-6)

  ,Date(MonthsEnd(1,Today(),-4),'MMMYYYY-') & Date(MonthsStart(1,Today(),-6),'MMMYYYY')

,

  IF( Date(Floor(tmpDate)) <= MonthsEnd(1,Today(),-7) AND Date(Floor(tmpDate)) >= MonthsStart(1,Today(),-9)

  ,Date(MonthsEnd(1,Today(),-7),'MMMYYYY-') & Date(MonthsStart(1,Today(),-9),'MMMYYYY')

  ,

  IF( Date(Floor(tmpDate)) <= MonthsEnd(1,Today(),-10) AND Date(Floor(tmpDate)) >= MonthsStart(1,Today(),-12)

  ,Date(MonthsEnd(1,Today(),-10),'MMMYYYY-') & Date(MonthsStart(1,Today(),-12),'MMMYYYY')

  ,'')))) as Test;

LOAD Date(makedate(2016)+recno() ) as tmpDate

AutoGenerate 800;

[luismadriz]

Yeap, you're right Stefan, Michael had misplaced parenthesis for the Date function. Good one!

Cheers,

Luis

[Tanalex]

Thanks for the tip Luis.  However, this is what we have.

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

[Tanalex]

Point taken Stefan.  My mistake not including working data.  That did the trick.  Those dang parentheses are going to be the death of me. 

That work exactly as I was hoping.  Thank you very much for your prompt solution!