Changing how data apprears, without changing the value

Hey Julie,

If you’re still okay sticking with the manual numbering mechanism there, I think your easy fix is to update your campaign_month value references to their newly transformed ones. For example, instead of

when ${table}.campaign_month = "201808" then 1

you can use

when ${table}.campaign_month = "Aug 2018" then 1

Alternatively, I’m sure you could get funky with loops to assign them.

What I tend to do with dates for easy manipulation and when there’s a precise way I want to show the date every time (as in your example above) is to see if there’s a way to get a datetime format so I can feed it into a dimension_group. Once it’s a dimension_group, you get access to all of those lovely timeframes contained within it

In your case, if you don’t have a date field available to you in the table, you can make one simply (written below for MS SQL 2012 dialect). The below would create dimensions for all the timeframes available (you can restrict it with timeframes), and always assume the first of the month for each of these records:

dimension_group: campaign_start {

type: time 

sql: DATEFROMPARTS(LEFT(${TABLE}.campaign_month,4),RIGHT(${TABLE}.campaign_month,2),1)

}

Then, you could make a simple formatted_campaign_month which truncates the month name to the first three digits, adds a space, then reveals the year, and sorts by the date:

dimension: formatted_campaign_month {

type:string

sql: CONCAT(LEFT(${campaign_start_month_name},3),' ',${campaign_start_year})

order_by_field: campaign_start_date

}