# Webi Line Chart showing First, Last, Min, and Max data values only (like Tableau)

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. Please vote up on this improvement request (or idea) which was created by Nadia Banihali, until that feature is added to Webi, please feel free to use the workaround in this 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:

1. 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).
2. Tool tip says Last Sales Revenue \$K
3. Tool tip says Min Sales Revenue \$K
4. Tool tip says Max Sales Revenue \$K
5. Tool tip says just Sales Revenue \$K (it will be the same 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 the tool tip 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

Final step to fix the vertical data value/number display problem (after the steps in the last picture) as shown, 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 symbols), 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 (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 (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% (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% (9th image in Step 5)

g. When we’re done formatting the 15 data series, let’s hide the Legend again (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, one benefit of that is, in the below image, we see that when one of the data points fell under both Last Sales Revenue \$K and Max Sales Revenue \$K, the tool tip shows Max Sales Revenue \$K, which is better and of high important. This happens because the tool tip for the last measure is displayed in such cases. Also, we can visually make out the First and Last data points easily.

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?

#### Please share your feedback in comments. Let me know if you see any typos and / or grammatical mistakes, and I’ll correct them.

This post first appeared on Web Service Testing A Blackjack GUI And API, please read the originial post: here

# Share the post

Webi Line Chart showing First, Last, Min, and Max data values only (like Tableau)

×