Colour Gradient in Expressions

De Qknow
Ir para: navegação, pesquisa

Introduction

EscalaCores1.PNG

Colours can represent more than values for dimensions. It's possible use colours to indicate how much important is a specific value in context. For example, in a bar chart representing sales by year, how much is profit or loss is so important as billing values. Using colours is possible show to decision users, either billing and profit/loss in same bar chart, just including colours to represent profit or loss. So, instead of create multiples charts and fill up the interface with a lot of objects, colours can be used from a lower to higher values.

By default, QlikView will paint each dimension value with a specific color using Color tab. But, is possible include calculations to create a automatic scale of colors to paint each dimension value from a perspective of profit or loss. This text show to you how to create variables, bar charts and formulas as expressions to use colors to represent a scale of values from dimensions. When finish this procedure you'll able to create a bar chart using different scale of colours.

If you need more assistance to use techniques as SET ANALYSIS, please use QlikView help or community knowledge. Also, if create variables as expressions is hard for you, read text about creating variables for repeatable formulas, here. If you are ready to know how to implement colours as expressions, you can load data below and use it as sample.

Before, create one new QlikView document using the QlikView Desktop 11 or higher. Copy the following data sample and paste it in script window accessible using CTRL + E shortcut. Save and run the script before create any object chart.



SalesReport:
LOAD * INLINE [
SalesYear, SalesTotal,       Cost
2000,         1937770,    1760181
2001,          712212,     717546
2002,          303098,     301762
2003,         1842440,    1668624
2004,         1652343,    1682694
2005,          455939,     417934
2006,           42664,      42524
2007,         1798303,    1606255
2008,         1544449,    1536734
2009,          769795,     741262
2010,          469678,     446608
];

Calculating Profit or Loss

Before create expressions for colours, it's necessary calculate profit or loss for each year. For that, add a new LOAD statement below sample data to create a new column that will be used to show us percentage variation between each year. For this new column create a alias as ProfitOrLoss.

LOAD SalesYear, (SalesTotal - Cost) / Cost As ProfitOrLoss Resident SalesReport;
ColoursScale01.PNG

In our example, variation is a percentage resulting between sales and cost of production. That is why a new column is necessary, to create a variation percentage by year. To example purposes, variation is (SALES - COST) / COST. After include the new LOAD statement, save and reload all data. Now, we're ready to create a new bar chart to represent sales by year.

Create a new bar chart by clicking right button and selecting New Sheet Object followed by Chart. In Chart Type select Bar Chart. Before click Next. Before you click next, enter a Window Title as Total Sales by Year in Window Title field. After it, click on Next button.

When Dimensions is showed to you, add SalesYear as Used Dimensions. Enter a new text for Label field, like just Year. Click on Next button to enter the first expression as:

=SUM(SalesTotal)
ColoursScale02.PNG

Using Label field, change the default text to Total Sales. Take advantage to select Values on Data Points option. After it, create a new expression clicking on Add button. Enter =AVG(ProfitOrLoss) as formula for that and enter Proft or Loss as Label. On this new expression named Profit or Loss uncheck Bar option and select bothText on Axis and Text as Pop-up.

Click on Next button three times up to Presentation tab. Uncheck Show Legend and go ahead. On Axes tab select Hide Axis. Advance to Number tab and click Integer for Total Sales expression. Select Profit or Loss and check Fixed to 1 Decimal. Advance to Layout tab clicking Next twice. For Border With change a value to zero (0 pt) and click Next again. Finally, enter Colors represent profit or loss as text for Title Text field.

ColoursScale03.PNG

Before close Create Chart Wizard, uncheck both Allow Minimize and Allow Maximize options. For Background Color on Inactive Caption and Active Caption, change their colors to white. Close New Chart Wizard to get something like that:

ColoursScale04.PNG

Configuring Colours's Scale

To create effect designed is necessary create three new variables at QlikView level. These can be created using Variable Overview window or can be set in load script. Open the Script Window and define three variables as the following code:

LET vRed = '=ARGB(255, 235, 102, 86)';
LET vGray = '=ARGB(255, 202, 201, 200)';
LET vGreen = '=ARGB(255, 11, 100, 43)';

Reload your script to new variables take effect. Now, from Variable Overview window set two more variables using the following definition:

  • One of them will be named as vMinBolha. Its value is going to 0.0025. That means what is minimal percentages expected when a product is selled. So, is expected at least 0.25%.
  • Create more one variable defined as vCorteBolha and put in it 0.2 value. This represents the maximum percentage for profit. This variable will be used in colour for expressions.


Close Variable Window and go to Bar Chart properties. From Expression tab select Total Sales and open its properties. Click on Backgroud Color to set a new expression for it. Using Edit Expression window, define the following formula that will change colours based on profit and loss.

ColorMix2
 (
  if(rangemin($(vCorteBolha), rangemax(Avg(ProfitOrLoss),0))<$(vMinBolha),
     -Sqrt(-(rangemin($(vCorteBolha),rangemax(Avg(ProfitOrLoss),0))-$(vMinBolha))/($(vMinBolha)-0)),
      Sqrt((rangemin($(vCorteBolha),rangemax(Avg(ProfitOrLoss),0))-$(vMinBolha))/($(vCorteBolha)-$(vMinBolha)))
     ), 
  $(vRed), 
  $(vGreen), 
  $(vGray)
 )

To finish some more configurations, accesses Style tab and change its orientation to horizontal. Return to Expressions tab and opens Profit and Loss properties. Change the Text Color attribute using the =Black() function as definition. Finished. Now each color will be defined using profit and loss percentages.

Complementary Changes

ColoursScale06.PNG

Some adjustments will turn your chart more readable and elegant. From Chart Properties, go to General tab and use Title Settings button to change Horizontal Alignment to Left. Click on Font button to change its size to 10 and its style to Regular.

After it, go to Presentation tab and update Cluster Distance to 2. To finish your job, add 4 new text objects. In one of them updates its content to Profit as the text. To another one, update to Loss. For these, sets transparent color as background. For last two boxes, update its colours to be like the following image.

EscalaCores7.PNG

ColoursScale08.PNG



Functions Used

  • SUM
  • COLORMIX2
  • RANGEMIN
  • RANGEMAX
  • AVG
  • ARGB
  • MAX
  • IF
  • NUM
  • SQRT



Idea 1.jpg

When a same expression is used in different objects you can create a variable to store its formula.

Variables can be defined with parameters and act as a typical function.. Learn here!


Envelope01.jpg
Need more? Mail Us!

Back | Summary | Main Page