Colour Gradient in Expressions
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
LOAD SalesYear, (SalesTotal - Cost) / Cost As ProfitOrLoss Resident SalesReport;
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 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
When Dimensions is showed to you, add
Used Dimensions. Enter a new text for
Label field, like just
Year. Click on
Next button to enter the first expression as:
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 both
Text on Axis and
Text as Pop-up.
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
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.
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:
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
vCorteBolhaand put in it
0.2value. 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.
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
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.
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!
|Need more? Mail Us!|