Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Two digit month number in expression

Hi All,

I have a field ORDER_CANCEL_DT and would like to use this as Year-Month in a pivot table.

Here is what I tried as my "Calculated Expression"

Year(ORDER_CANCEL_DT)&'-'&Month(ORDER_CANCEL_DT)

But the issue is it messes up the sorting

2012-1 , then 2012-10 , then 2012-2 and so on

2012-1
2012-10
2012-2
2012-3
2012-4
2012-5
2012-6
2012-7
2012-8
2012-9

Is there any way we could extrract the two digit month from a date field?

ex. 2012-01, 2012-02 and so forth?

Thanks,

Aji Paul.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try

Year(ORDER_CANCEL_DT)&'-'& num( Month(ORDER_CANCEL_DT), '00')

but I would suggest to use a formatted date:

Date( monthstart(ORDER_CANCEL_DT), 'YYYY-MM')

View solution in original post

5 Replies
swuehl
MVP
MVP

Try

Year(ORDER_CANCEL_DT)&'-'& num( Month(ORDER_CANCEL_DT), '00')

but I would suggest to use a formatted date:

Date( monthstart(ORDER_CANCEL_DT), 'YYYY-MM')

Not applicable
Author

Awesome!

Thank You verymuch!

whiteline
Master II
Master II

Hi.

You could use Num() to change text format: Num(Month(ORDER_CANCEL_DT), '00')

Actually, you don't have to construct a string representation for your date manually.

Use formatting functions Date() and so on.

The only thing you should keep in mind is that there is numeric value and text representation of dates.

Formatting functions don't change the numeric value.

So if you want to discard the days information in text representation you should also discard it from numeric value.

For example:

Date(MonthName(ORDER_CANCEL_DT), 'YYYY-MM')

MonthName - discards days, replacing it with 01 and change text format.

Date - changes the format again.

Not applicable
Author

Thank You for the details

JaWa
Contributor II
Contributor II

Dear Stefan,

 

many thanks! Sometimes small tweaks have the greatest effect!

 

Jan