Dynamic date hierarchy selection

Learning Center Articles
3

The issue is with the formula “MyOrderDateKeySort”:
parseInt(concat(toString(extract(‘YYYY’,{Order Date})),toString(extract(‘MM’,{Order Date})),toString(extract(‘DD’,{Order Date}))))

The parameters
extract(‘MM’,{Order Date}) and
extract(‘DD’,{Order Date})
return single digit results for values less than 10.

For example, 4 January 2023, gets converted to 202314 instead of 20230104.
The result is that all Q4 dates, regardless of year, have a higher value than dates in Q1, Q2 or Q3.

I have tweaked the formula as follows:
parseInt(
concat(
toString(extract(‘YYYY’, {Order Date})),
ifelse(
strlen(toString(extract(‘MM’, {Order Date}))) = 1,
concat(‘0’, toString(extract(‘MM’, {Order Date}))),
toString(extract(‘MM’, {Order Date}))
),
ifelse(
strlen(toString(extract(‘DD’, {Order Date}))) = 1,
concat(‘0’, toString(extract(‘DD’, {Order Date}))),
toString(extract(‘DD’, {Order Date}))
)
)
)

Now, the ordering is correct.

1 Like