That’s correct, we can create a Line Chart in Webi report, which shows First, Last, Min, and Max data values only (almost similar to Tableau).
I got this idea when I was thinking about providing a workaround to the requirement in this Webi question posted by Lieselot Ver Eecke, and within a few days, one of our users requested a similar update to their Webi dashboard. Request: Please vote up on this improvement request (or idea) to show selected data values, created by Nadia Banihali. Until that feature is added to Webi, this workaround would be of great help.
Here is how the report looks, when we’re half way through the blog.
We’ll be using BO 4.2 SP6 (latest version as of today), but this can be created in BO 4.2 SP2, and may be in few of the earlier versions.
Here are the steps:
Step 1: Create a simple report
a. Create a simple report using eFashion universe, pull in Year, Month, Month Name, and Sales Revenue objects.
Step 2: Creating the required variables (9 in total)
Very important: Please name the variables as I did, don’t use any custom conventions (at least for this exercise).
a. To make data values small, create a measure Sales Revenue $K to show Sales Revenue in multiples of thousand, and round it off to zero decimal.
- Sales Revenue $K =Round([Sales revenue]/1000;0)
b. Create variables that return First and Last Months by Year.
- First Month by Year =Min([Month]) In ([Year])
- Last Month by Year =Max([Month]) In ([Year])
c. Create a variable that returns Sales Revenue $K for first month only, and nothing for the other months. Create a similar variable Sales Revenue $K for last month only. (Can’t we do it in one variable? Let this be your question 1, we’ll get the answer later.)
- First Sales Revenue $K =If([Month]=[First Month by Year]) Then [Sales Revenue $K]
- Last Sales Revenue $K =If([Month]=[Last Month by Year]) Then [Sales Revenue $K]
Very important: Please don’t specify Else 0 in the above formulas, we don’t want 0’s, we’re better off with Nulls.
d. Let’s create a variable that returns Min Sales Revenue for each Year, and another one for Max Sales Revenue for each Year.
- Min Sales Revenue by Year =Min([Sales Revenue $K] ForEach ([Month])) In ([Year])
- Max Sales Revenue by Year =Max([Sales Revenue $K] ForEach ([Month])) In ([Year])
e. Create a variable that returns Sales Revenue $K for only the month that has the min Sales Revenue $K out of all the available months, and nothing for other months. Create a similar variable for max Sales Revenue $K. (Can’t we do it in one variable? This is related to your question 1, we’ll get the answer later.)
- Min Sales Revenue $K =If([Sales Revenue $K]=[Min Sales Revenue by Year]) Then [Min Sales Revenue]
- Max Sales Revenue $K =If([Sales Revenue $K]=[Max Sales Revenue by Year]) Then [Max Sales Revenue]
Step 3: Chart layout formalities
a. Insert a Dual Axis Line Chart (or Line Chart with 2 Y Axes), and assign the data as shown. Please make sure to select measures in Value Axis 2 in the same order as shown below. (Do we have to follow the order? Let this be your question 2, we’ll get the answer later, after we add more data to the chart.)
b. After the Chart is displayed, increase its dimensions, Width 25cm and Height as 14cm.
c. First things first, 1. Hide the lengthy Title on top, 2. Hide Value Axis 2 on right, its redundant, we see a similar scale on the left as well, and 3. Display Axis line (the vertical scale line) for Value Axis on left.
The chart looks much better now.
Step 4: Fun begins, early signs of bonus features
Hovering the mouse over the 5 data points (shown below) reveals a tiny but awesome detail in the tool tips.
We know all the data points are for Sales Revenue $K, but:
- Tool tip says First Sales Revenue $K, which is correct, makes sense logically, and is much better than just saying Sales Revenue $K (which will show up by default for all data values/data points in any chart).
- Tool tip says Last Sales Revenue $K
- Tool tip says Min Sales Revenue $K
- Tool tip says Max Sales Revenue $K
- Tool tip says just Sales Revenue $K (it will be the same for all the remaining data points, if they’re not from First, Last, Min or Max)
Answer to question 1: Tool tip was the reason, why I didn’t want to use custom names or other naming conventions for the variables.
Don’t you think the users will be happy to see this bonus feature in tool tips? Even though Webi doesn’t give us control to customize tool tips yet, we’re kind of customizing them already. Just so you know, Custom tooltip is included in Product Road Map of Webi in 2019.
Step 5: Let’s add some colors
Wouldn’t it be nice to format Max Sales Revenue $K symbol with Green color, to make it stand out?
a. Select and right click on Max Sales Revenue $K value in the chart legend, and click Format Data Series.
In the Format Data Series window, update the options as shown, and click OK.
b. This is how the Chart looks now, that Green symbol for Max Sales Revenue $K data value looks good, but the data value/number being displayed vertically doesn’t, let’s fix that.
Right click on the Chart, select Format Chart, and follow the steps as shown
Follow the next (very important) step to fixing the vertical data value/number display problem, and click OK.
The Chart looks better now.
c. Let’s wind up the coloring task, Format Data Series for Min Sales Revenue $K as shown (Red would be great for displaying the culprit, min value), and click OK.
Let’s Format Data Series for Sales Revenue $K to make it’s symbol a little lighter, by setting opacity to 50% as shown (and also copy its color code).
d. Let’s Format Data Series for both First and Last Sales Revenue $K (as shown), as we did for Min and Max Sales Revenue $K. With a subtle difference that, we’ll use the color code copied from Sales Revenue $K (in previous picture), and also set their opacity to 0%, because a symbol due to Sales Revenue $K already exists in the background. Also, we want the symbols for just Min and Max Sales Revenue $K to stand out.
Remember, the steps in the next image need to be done twice, 1st time for First Sales Revenue $K, and 2nd time for Last Sales Revenue $K.
This is how the chart looks like. Awesome, isn’t it?
Step 5: Clean up for the initial cut
a. Let’s make the (trend) line thinner, to do so, update Format Data Series options for Sales Revenue $K as below.
Let’s remove the legend. Right click on the Chart, select Format Chart, and update as shown
Here’s the result, but we’re not done yet.
Step 6: Multiple (trend) Lines, one each for different year
a. Let’s add some more data to the chart, right click on the Chart, select Assign data, and in the Assign data window, add Year under Region Color as shown, and click OK.
Oops, the chart is messed up, specially the coloring we did to the different symbols/data series, we’ve a lot of cleanup to do. But the good news is, we see First, Last, Min, and Max data values being displayed for each year.
b. Let’s display the Legend, and format all the data series again. To bring back Legend, right click on Chart, select Format Chart, and follow the steps as shown.
There are quite a few data series we need to Format. We’ll need to update just the colors and opacity, the other options/properties were not affected.
c. Let’s format Max Sales Revenue $K data series for all 3 years, by changing the color to Green (please follow steps in the 2nd image in Step 5)
d. Let’s format Min Sales Revenue $K data series for all 3 years, by changing the color to Red (please follow steps in the 7th image in Step 5)
e. Let’s format Sales Revenue $K data series for all 3 years, by changing the color to RGB: 116, 171, 226 and opacity to 50% (please follow steps in the 8th image in Step 5)
f. Let’s format First and Last Sales Revenue $K data series for all 3 years, by changing the color to RGB: 116, 171, 226, and opacity to 0% (please follow steps in the 9th image in Step 5)
g. When we’re done formatting the 15 data series, here is what the chart looks like.
h. Let’s hide the Legend again (please follow steps in the 2nd image in Step 5), and this is how the chart looks like now. Voila!
Answer to question 2: Selecting measures in Assign data in a specific order was necessary, we see it’s benefit here. In the below image, when one of the data points fell under multiple categories, Last Sales Revenue $K and Max Sales Revenue $K, the tool tip shows Max Sales Revenue $K, which is of higher importance between First and Max. This happens because the tool tip for the measure at the bottom (in Assign Data window) is displayed in such cases. Also, we can easily figure out the First and Last data points, visually, we don’t need them to be highlighted.
Bonus: The possibilities with this workaround are many, it’s not limited to just showing First, Last, Min, and Max data values, we can also show data values only for Top N and/or Bottom N, and so on, even in a Bar or Column chart. Now, that’s cool, isn’t it? 🙂
Update 01 (07/30/2018 12:00 pm EST) – This section updated again as of 09/27/2018 09:59 am EST.
Building up on this idea by overlapping 2 (single axis) Line Charts, we can make the Chart look like below, we’ve achieved the exact look when compared to Tableau, as it’s displaying symbols / dots only for the First, Last, Min, and Max data points. Also, look at Bonus at the end of this update.
Data points highlighted by Green circles shows symbols / dots, and the ones highlighted by Red circles don’t.
We’ll need 2 Line Charts to do this, below steps show what needs to be done to those 2 charts.
Line Chart 1 Details:
- Use the Line Chart selected and assign data as shown below
- Data Label Displaying Mode to be Checked and set Spacing to 8, as shown below
- Marker -> Line Symbol should be Checked, as shown below. Click Apply and/or OK in the Format Chart window.
After the above 3 steps, this is how the chart should look like.
Line Chart 2 Details:
- Use the Line Chart selected and assign data as shown below, please note that we’re using Sales Revenue $K measure as well in Line Chart 2 (we didn’t use it in Line Chart 1)
- Data Label Displaying Mode to be Unchecked, as shown below
- Make sure that Marker -> Line Symbol should be Unchecked, as shown below
- As we’re going to overlap the 2 Charts, with Line Chart 2 on the top, we need a transparent background on Line Chart 2, set its Opacity to 0% in the Background -> Background Color option, as shown below. Click Apply and/or OK in the Format Chart window.
After the above 4 steps, this is how the chart should look like
Overlapping the 2 Line Charts
- I’ve named the charts, Line Chart 1 – To be in the Back and Line Chart 2 – To be in the Front
- To bring the Line Chart 2 in front, Right Click on it, from the Options, go to Order -> and click Bring to Front, as show below.
- Make sure that both the Line Charts have same Sizes, as shown below
- To overlap them, set the Layout of Line Chart 2 – To be in the Front as shown below
Voila! This is how overlapped charts look.
Exactly what we wanted, dots/markers and data values displaying on First, Last, Min & Max values only.
Moving your mouse over different data values shows different/perfect text in the tool tip, related to the value and month
Sep: Max Sales Revenue $K : 4,061
Aug: Min Sales Revenue $K : 1,564
Jan: First Sales Revenue $K : 3,840
Dec: Last Sales Revenue $K : 3,090
For all other months, circled in red it shows, Sales Revenue $K.
Pretty sleek, isn’t it?
Another cool interactive option I added by building up on this idea is, giving the users flexibility to choose from an input control to show data values for Top 3 or Top 5 or All months, and it works flawlessly. I’ll add screenshots when I get a chance.
End of Update 01