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?
Already have an account? Login
Login to the community
No account yet? Create an account
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
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
you can use
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.
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:
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:
This will be helpful.