Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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')
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')
Awesome!
Thank You verymuch!
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.
Thank You for the details
Dear Stefan,
many thanks! Sometimes small tweaks have the greatest effect!
Jan