Changing how data apprears, without changing the value

Im hoping someone has a better solution than I had. I have a data field called Campaign_month that is numeric and easy to sort. But it didn’t look Great on the dashboards. so I changed it to look better. That, however, broke my sort, and I can’t run max/min on it. Any suggesstions on haveing the Visual of April 2019 while still having the numeric functionality?

4 replies

Userlevel 5

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.

Thank you, Ben. If it its possible, I would like to get rid of the manual count set up, if its possible. I need to create a system, that will be self regulating into the future. i.e. not having to type in each month as we leave this current year.

Userlevel 5

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 {
sql: CONCAT(LEFT(${campaign_start_month_name},3),' ',${campaign_start_year})
order_by_field: campaign_start_date

This will be helpful.

Thank you.