Formatting Monthly Date Labels Without Days

Question & Answer
, , ,
1

I have a calculated field that switches between different date granularities using a parameter. The base calculation works, but I need help formatting the monthly view to show only Month Year (e.g., “Jan 2024”) instead of the full date.

here is how im doing it now :

ifelse(
  ${TimeGranularity} = 'Monthly', truncDate('MM', {created_at}),
  ${TimeGranularity} = 'Weekly', truncDate('WK', {created_at}),
  {created_at} 
)
2

Hi @Mustaf

Welcome to the QuickSight community!

You can simplify the logic for extracting the month abbreviation using a switch statement and add this to the ifelse statement.

Example: (Replace the fields from your dataset)

concat(
  switch(
    extract('MM', truncDate('MM', OrderDate)),
    1, 'Jan',
    2, 'Feb',
    3, 'Mar',
    4, 'Apr',
    5, 'May',
    6, 'Jun',
    7, 'Jul',
    8, 'Aug',
    9, 'Sep',
    10, 'Oct',
    11, 'Nov',
    12, 'Dec',
    'Unknown'
  ),
  ' ',
  toString(extract('YYYY', OrderDate))
)
1 Like
3

Hi @Mustaf

To extract week number from date use the following calculation.

Example: (Replace the fields from your dataset)

ifelse(
    extract("WD", OrderDate) = 1,
    dateDiff(truncDate("WK", truncDate('YYYY', OrderDate)), OrderDate, 'WK'),
    dateDiff(truncDate("WK", truncDate('YYYY', OrderDate)), OrderDate, 'WK')+1
)

Following calculated field switches between monthly and weekly date granularities using a parameter.

ifelse(
  ${TimeGranularity} = 'Monthly',
concat(
  switch(
    extract('MM', truncDate('MM', OrderDate)),
    1, 'Jan',
    2, 'Feb',
    3, 'Mar',
    4, 'Apr',
    5, 'May',
    6, 'Jun',
    7, 'Jul',
    8, 'Aug',
    9, 'Sep',
    10, 'Oct',
    11, 'Nov',
    12, 'Dec',
    'Unknown'
  ),
  ' ',
  toString(extract('YYYY', OrderDate))
),
 ${TimeGranularity} = 'Weekly',
toString(
ifelse(
    extract("WD", OrderDate) = 1,
    dateDiff(truncDate("WK", truncDate('YYYY', OrderDate)), OrderDate, 'WK'),
    dateDiff(truncDate("WK", truncDate('YYYY', OrderDate)), OrderDate, 'WK')+1
)),
''
)

Month or Week

1 Like
4

It works, Thanks a lot

5

Hi @Mustaf

Thanks for confirming!

6

An issue i have noticed is that the month is not ordered correctly
i think it oreder it alphabitcally ? it start with august then decembre …etc

7

You can use an off-visual field to do the sorting. I prefer creating a sortkey for such scenario.

An example for the sortkey I prefer using is :

extract(‘YYYY’,datefield)*10000 + extract (‘MM’,datefield)*100 + extract(‘DD’, datefield)

This may change based on your use case.

And, last step is to set the aggregation to min while configuring sorting through off visual field.

1 Like
8

What do you show for week?

9

Hi @Mustaf,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

10

im still cant get the 3 TimeGranularity correctly where monthly lables should have only month/year
and weekly have day/month or number of the week start from 1 to 52
and daily have day/month/

11

I want for months to be Month only or month/year
for weeks to be day/month
for day to be day/month