topic Re: Two digit month number in expression in QlikView

Two digit month number in expression

Wed, 24 Oct 2012 18:22:48 GMT

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

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.

swuehl — Wed, 24 Oct 2012 18:27:26 GMT

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

Wed, 24 Oct 2012 18:32:41 GMT

Awesome!

Thank You verymuch!

whiteline — Wed, 24 Oct 2012 18:33:17 GMT

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.

Wed, 24 Oct 2012 19:11:38 GMT

Thank You for the details

JaWa — Tue, 29 Oct 2019 06:34:22 GMT

Dear Stefan,

many thanks! Sometimes small tweaks have the greatest effect!

Jan