Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Tanalex
Creator II
Creator II

Odd numbers in DATE field output

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):

GoodR3M.jpeg

  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

BadR3M.jpeg

  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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

5 Replies
luismadriz
Specialist
Specialist

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
MVP
MVP

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
Specialist
Specialist

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

Cheers,

Luis

Tanalex
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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!