HSL - script and chart function

Overview

Open the Data load editor and add the load script below to a new section.

The load script contains:

• A dataset which is loaded into a data table called Example.

• The following fields in the data table:

  • Product

  • Sales

Load script

Example:
LOAD * Inline [
Product, Sales
Product A, 200
Product B, 500
Product C, 100
Product D, 300
Product E, 700
];

Results

Load the data and open a sheet. Create a new table and add this field as a dimension:

• Product

Create the following measure:

• =Sum(Sales), to calculate the sum of sales.

In the Properties panel of the Sum(Sales) measure, enter the following expression as the Background color expression. This will apply cell color according to the sales calculation for the product.

• =If(Sales > 400, HSL(.33, 1, 0.25), If(Sales >= 200 and Sales <= 400, HSL(.167, 1, 0.5), HSL(0, 1, 0.5)) )

In this example, observe the following:

• Sales values over 400, such as Product B and E, are highlighted with a green background.

• Sales values between 200 and 400, such as Product A and D, are highlighted with a yellow background.

• All other sales values are highlighted with a red background.

Overview

A dataset contains sales figures. We want to analyze the data and highlight profit margins according to whether they are low, moderate, or high.

Open the Data load editor and add the load script below to a new section.

The load script contains:

• A dataset which is loaded into a data table called Example.

• The following fields in the data table:

  • Customer

  • SalesValue

  • Cost

  • ProfitMargin

Load script

Example:
LOAD * Inline [
Customer, SalesValue, Cost, ProfitMargin
Customer A, 500, 300, 40%
Customer B, 800, 750, 6.25%
Customer C, 400, 300, 25%
Customer D, 600, 550, 8.33%
Customer E, 900, 700, 22.22%
];

Results

Load the data and open a sheet. Create a new table and add this field as a dimension:

• Customer

Create the following measures:

• =Sum(SalesValue), to calculate the sum of sales.

• =Sum(Cost), to calculate the total value of the cost of sales.

• =Sum(ProfitMargin), to sum the profit margin. To show this value as a percentage, under Number formatting, select Number > Formatting Simple > 12.34%.

In the Properties panel of the Sum(ProfitMargin) measure, enter the following expression as the Background color expression.

If(ProfitMargin > 0.3, HSL( 0.33, 1, 0.25),   // Green for high-profit customers
  If(ProfitMargin >= 0.1 and ProfitMargin <= 0.3, HSL( 0.11, 1, 0.5),   // Orange for moderate-profit customers
    HSL(0, 1, 0.5)   // Red for low-profit customers
  )
)

In the Appearance panel, under Presentation, turn off Totals.

The output from the HSL function renders the following colors according to the customer's profit margin: 

• Green, HSL( 0.33, 1, 0.25), for customers with profit margins higher than 30%.

• Orange, HSL( 0.11, 1, 0.5), for customers with moderate profit margins between 10% and 30%.

• Red, HSL(0, 1, 0.5), for customers with a profit margin below 10%.

Overview

A dataset contains actual and projected sales data by country. A sales manager wants to display the data in a bar chart and highlight those sales figures that are higher than the projected value for the period.

Open the Data load editor and add the load script below to a new section.

The load script contains:

• A dataset which is loaded into a data table called Example.

• The following fields in the data table:

  • Country

  • ActualSales

  • ProjectedSales

Load script

Example:
Load * Inline
[Country, ActualSales, ProjectedSales
Sweden, 100000, 50000
Germany, 125000, 175000
Norway, 74850, 68500
Ireland, 45000, 48000
Sweden, 98000, 50000
Germany, 115000, 175000
Norway, 71850, 68500
Ireland, 31000, 48000
];

Results

Load the data and open a sheet. Create a new bar chart and add this field as dimension:

• Country

Create the following measure:

• =Sum(ActualSales), to calculate the total value of sales for the country.

Add a color expression to compare actual sales with projected sales. In the properties panel, under Appearance > Colors and legend, turn off Colors: Auto to switch to Custom, and then select By expression from the list. Enter the following expression:

• =If(Sum(ActualSales)>Sum(ProjectedSales),HSL(1.2,1.0,0.25),HSL(0, 1.0, 0.25)), to highlight in green the occurrences where actual sales exceeds projected sales and in red where projected sales exceeds actual sales.

We can see that the bar color is green for those countries with sales that exceed the projected value, whereas those with sales below the projected value appear red. For example, Sweden is green because its sales value of 98,000 is greater than the projected value of 50,000.