The addition of “Derived Attributes” constitutes a massive upgrade to the v10 Visual Insight tool, but I had a hard time wrapping my head around it conceptually. It wasn’t until I ran into a couple of specific situations where I really started to understand some new possibilities that weren’t available in the old Visual Insight. Hopefully, this post helps you see a situation or two where derived attributes might save you some headache.
Here’s one such problematic scenario:
My data cube for my dashboard brings back daily sales data. Every day it adds data for yesterday. For one of the dashboard tabs, however, users need to see JUST YESTERDAY’S sales. Using regular Visual Insight filters on a day field, you’re stuck hardcoding dates. So what ends up happening is I log in to the dashboard on 9/24. I go over to the filter tab and alter the filter to see data for 9/21-9/23.
I log in again on 9/25. Oops, the tab that’s supposed to show “the last three days,” is still holding the selection from before – 9/21-9/23. So I have to manually log into the Visual Insight dashboard and move the filter forward. Every day this happens. I have to log in and change the filter every single day to move it forward by one day.
To be clear, there are some database-level and/or MicroStrategy-Architect-level ways to solve this, but since this is SSBI, I want to resolve it without leaving the comfort and ease of the v10 dashboard editor. Below you’ll see my solution summarized in one screenshot.
One note – since I’m using tutorial data, there’s no data in the database for the last three days. Hopefully you can see the logic that if I set the filter for -1 to -3, the result would be a rolling 3-day timeframe.
And don’t worry, it still works when you don’t display the derived attribute “Days Ago” on the grid as well:
Below, I’ve included some more detailed steps and screenshots for readers to follow, but for the more advanced users, the one-sentence-version for this solution goes like this:
Create a derived attribute with definition
“DaysBetween(CurrentDate(), [Day@ID])” and filter by that new attribute to limit the grid to a set number of days from the current date.
This is extremely helpful and requires no more privileges than what’s already granted to every user of the Visual Insight editor in MicroStrategy. I could imagine some ways to extend this, such as to grab the days between some fixed date of some date that comes from the database (such as last updated date) to remove uncertainties when data doesn’t update daily.
In case you’re not on v10 yet, I have a slightly less satisfying solution to this issue for v9. The problem is that you have to include the day attribute on the visualization for it to work. Scroll down to the bottom of this post to see what I mean.
Here are those detailed steps I promised:
1. Create an attribute by clicking on the dataset dropdown:
2. Use the following formula (or customize it for your own needs):
3. Add that attribute to the filter, and move the slider to allow the number of days you want. Remember, the tutorial dataset I’m using doesn’t have dates after 2014, so you’ll probably want to change yours to something like -1 to -3 in your environment for last 3 days.
Solution for MicroStrategy v9 Users
The old, less awesome way to handle this situation in v9 is shown below. I say it’s less awesome because it only works when I include the order date in the visualization objects.
This is because the filter has to be based off a derived metric in v9. A derived metric that uses an attribute in its definition needs to “see” that attribute in the grid/graph. This was accomplished with a “Days Ago” metric that is defined in the following way:
(Rank<ASC=False> ([Max (Order Date)]))
Max of Order Date does not need to be defined as a separate metric. I only included that in the screenshot to show what that calculation does.