1 2 3 Previous Next

Qlik Design Blog

63 Posts authored by: Jennell McIntire

One of the new features in the April 2018 release of Qlik Sense was the addition of the NumericalAbbreviation variable.  This variable allows users to specify the numeric abbreviations that are used when a numeric value has an Auto number format.  This was great because by default, G is used to indicate billions and my preference is to use B.  Now, I can do that by editing the NumericalAbbreviation variable in my script.  If you are familiar with scripting, you know that the first few lines on the Main tab in the script are SET variable statements that indicate what abbreviations and values you would like to use to represent certain data in your app.  For example, in the script below, the MonthNames variable is set to how I would like to abbreviate months in my app.

SET month.png

With the NumericalAbbreviation variable, I can specify how I will like the numeric abbreviation to appear.  By default, it is set like this:

SET default.png

If you created an app using a release prior to the April 2018 release, you will not see this variable on the Main tab in your script but you can add it to your script along with the other variables statements.  The easiest way to do this is to copy and paste it from Qlik Help.

 

In the app screenshot below, you can see the G used in the Total Gross KPI and in the Top 10 Theaters bar chart (bottom left).

G-sheet.png

Since my preference is to use the B instead of G to show billions, I changed the NumericalAbbreviation variable to be:

SET - Copy.png

Now the app looks like this:

B-sheet.png

A small, easy tweak to the script, allowed me to change the numeric abbreviation in the app.  I should note that the numeric abbreviation is used only when Numbering Format is set to Auto.  So, if the Numbering Format is set to something other than Auto such as Money or Number, the NumericAbbreviation variable is not used and the format that you specify in the properties of the object is used.

 

In this blog, I showed one example of how the NumericalAbbreviation variable can be used but it can also be used to add spacing before the abbreviation or other custom abbreviations, if you choose.  This seems like a small feature but it has a large impact providing users with more flexibility and control over how the data is displayed.  To learn about other new features released in April, check out Michael Tarallo’s Qlik Sense – What’s New April 2018 video.

 

Thanks,

Jennell

Jennell McIntire

Nested Loops

Posted by Jennell McIntire May 4, 2018

There are various loops/control statements that can be used in a script to control script execution.  Some common ones include:

  • If…then
  • Do…loop
  • For…next
  • For each…next

It is also possible to use one loop/control statement within another loop.  In the script I am going to review in this blog, I used all of these control statements except the do…loop to generate the rank of a company, by region, over 8 years.  Let me begin by setting the scene and explaining what I am trying to do.  I had 8 years’ worth of company data that included the company's overall rank (in a Data table).  Let’s say the overall rank is the company’s rank based on sales across all regions for a given year.  My goal is to get a company’s rank for their specific region, each year, based on the overall rank.  For example, in the example data table below Company C may have an overall rank of 3 in 2017 but in its region, North America, it is the top company so it has a regional rank of 1.

 

Example Data

YearCompanyOverall RankRegionRegional Rank
2017Company A1Europe1
2017Company B2Europe2
2017Company C3North America1
2017Company D4Central America1
2017Company E5North America2
2017Company F6Europe3
2017Company G7Europe4

 

What I would like to do via the script is determine the regional rank for each company, each year, so I can use it in a KPI object in an app.  Here is the script that I am using to accomplish this:

script.png

Let’s step through the script step by step.

  1. First, I use a For each…next loop to loop through each region (line 1). 
  2. Then I use a For…next loop to loop through the years (line 3).  Since I knew what years of data I had, I hard-coded the years but you can also use variables for the years which is a good idea if the years may vary. 
  3. My next step is to use an If…then statement (line7) to determine if this is my first time through the loop.  If it is the first time, year will be 2010 and the region will be Africa so this is what I check for in the If...then statement.  If this is the first time through the loop, I create a Temp table that has the key field, CompanyRegionKey, and the field, Regional Rank, which I calculate using the RowNo() function.  The RowNo() function returns the position of the current row in the resulting table starting at 1.  Since I am sorting the table by Rank, the rows will be added to the table in the regional rank order.  You can learn more about the RowNo() function in my RecNo or RowNo? blog.  If this is not the first time through the loop, then I create the Temp table and I use the noconcatenate prefix so that the data is not concatenated to the already existing Temp2 table (which has the same fields). 
  4. My next step is to create (line 26) or concatenate to (line 29) the Temp2 table which will store the key field and the new Regional Rank field. 
  5. After this step, I Drop the Temp table (line 32).  I remove the Temp table from the data model and recreate it every time I loop through the script so that RowNo() always starts at 1.  If I did not delete the Temp table and concatenated the data to it, the RowNo() function will pick up where it left off instead of starting over at 1.
  6. For each region, I loop through all the years and create and save the Regional Rank.  Once all the years are complete for a region, the script will go to the next region in the For each…next loop. 
  7. Once all regions and years are complete, I Left Join the Regional Rank field to my Data table and then Drop the Temp2 table from the data model.  I now have a Regional Rank value for each company in my Data table.

Save yourself time and lines of script by using loops when you need to repeat one or a series of statements in your script.  Loops can be nested, as I did in the script above, or used one at a time.  Instead of repeating the same statements for each year and each region, use nested loops so the statements in the script only need to be written once.  This also makes maintenance easier.  In the future, if a change needs to be made to the script, it only needs to be done once.  Check out other blogs about loops with Henric Cronstrom’s blog on Loops in the Script and my blog on the Do…Loop.


Thanks,

Jennell

Jennell McIntire

Replace() Function

Posted by Jennell McIntire Apr 6, 2018

The Replace() function can be used in the script or a chart to replace all occurrences of a sub-string with another sub-string within an input string.  Basically, you can replace a character or string of characters in a field or input string.  The syntax from Qlik Help looks like this:

 

Replace(text, from_str, to_str)


The first parameter, text, is a string.  This can be a field or some text.  The second parameter, from_str, is the string you would like to replace in the input string.  This can be a single character or a string of characters.  The third parameter, to_str, is the string that should replace the second parameter.  If the from_str is not found in the text then nothing is replaced.  The Replace() function works from left to right.

 

I have used the Replace() function when I have a field that has some characters that I do not need.  For example, I was loading names in a recent project and some of the names had a leading underscore character (_).  Since this was the case with only some of the names and not all, I used the Replace() function to replace the underscore with an empty string using syntax like this:

replace.png

You can also nest the Replace() function.  I have used this if there is more than one character I would like to replace in a string.  The nesting works from the inside out so keep this in mind as it may affect your results.  In the table below are a few examples of how Replace() can be used.

table.png

  • Column 1 - shows the original text string for Product.  This is the first parameter for all the Replace() examples.
  • Column 2 – replaces the underscore with a space.
  • Column 3 – is an example of a nested Replace() function.  First it replaces the underscore with an empty string.  Then it looks at the result of that string and replaces the question mark with an empty string.  The last step is to look at the results of that string and replace underscore question mark (_?) with a space (which will not be found in the Column 3 example).
  • Column 4 – is another example of a nested Replace() function but notice how the order affects the results for the input string “Product_?E”.  Since the first Replace() function to be evaluated replaces the “_?” with a space, the next two Replace() functions do not find the second parameter to replace.  Therefore, the result is “Product E” with a space before “E.”  In Column 3, the result is “ProductE,” without a space, since the underscore and the question mark were replaced individually with an empty string before the Replace(‘_?’, ‘ ‘) part of the expression was executed.

 

While my example shows how the Replace() function can be used in a chart expression, I always use it in the script to handle any replacements as I load the data.  What is nice about the Replace() function is it does not modify the text if the sub-string cannot be found.  With that in mind, you should make sure you want to replace all occurrences of the sub-string because there is not an option to pick and choose which sub-string occurrences to replace - all of them will be replaced.

 

Thanks,

Jennell

As a member of the Demo Team, I often use random, dummy data to build demos.  If the data is not exactly how I would like it to be, I can tweak it to best illustrate Qlik products and features.  One of the tweaks that I often make is to update the dates in the data model – I take an old data set from a few years ago and refresh it so it looks more current.  The AddYears and AddMonths functions make this easy to do.

 

The AddYears and AddMonths functions can be used in the script and in a chart.  Here is how the AddYears function is defined in Qlik Sense Help.

 

AddYears

This function returns the date occurring n years after startdate or, if n is negative, the date occurring n years before startdate.

 

Syntax:

AddYears(startdate, n)

 

The AddYears function adds or decreases a date by the number of years indicated in the second parameter.  If the parameter is positive, the date is increased, and if the parameter is negative, the date is decreased.  In the table below, the Date column has the original date.  The AddYears(“Date”, 5) column increases the date by 5 years.  The AddYears(“Date”, -5) column decreases the date by 5 years.  You can see how easy this to use to refresh the dates in your data model.

AddYears.png

The AddMonths function works the same way except the AddMonths function has a third parameter to set the mode.  The syntax looks like this:

 

AddMonths

This function returns the date occurring n months after startdate or, if n is negative, the date occurring n months before startdate.

 

Syntax: 

AddMonths(startdate, n , [ , mode])

 

The mode, as defined in Qlik Sense Help, “specifies if the month is added relative to the beginning of the month or relative to the end of the month. If the input date is the 28th or above and mode is set to 1, the function will return a date which is the same distance from the end of the month as the input date. Default mode is 0.”

 

Let’s look at some examples.  In the table below, the AddMonths(“Date”, 3) column add 3 months to the date in the Date column and AddMonths(“Date”, -3) shows a date 3 months prior to the date in the Dates column.  By default, mode is 0 so by excluding it I am assuming the default value.  So you can see that AddMonths(“Date”, 3, 0) returns the same results as AddMonths(“Date”, 3).  If I set the mode to 1, AddMonths(“Date”, 3, 1), it will take into account the distance of the date from the end of the month when the day is the 28th or above.  You can see that for the Dates 1/1/2013 and 1/15/2013, AddMonths(“Date”, 3, 1) returns 4/1/2013 and 4/15/2013, respectively, as you would expect.  But for 1/30/2013, the results are a little different.  Since the 30th is past the 28th day of the month, the function checked to see how many days from the end of the month the day falls.  In this case, the 30th is 1 day from the last day of the month (which is the 31st for January).  So the results of AddMonths(“Date”, 3, 1) will return a date 3 months later and 1 day from the last day of the month, hence 4/29/2013 since there are 30 days in April.

AddMonths.png

The AddYears and AddMonths functions can also be used to create a new date in your data model.  For instance, if you need to create a new date field that is one month after a transaction date, you can do that in the script with an expression like this:

 

AddMonths(“Transaction Date”, 1) as “New Date Field”

 

There are many ways these functions can be used in your app.  The one thing to keep in mind is if you are going to increase or decrease the dates in your data model, you should do so consistently.  Make sure you are updating all dates in the data model by the same time frame.  This way, date relationships in the data model are maintained throughout the app.  You may also need to update other date related fields like day, week, month, year, etc. if you are using these fields in your app.

 

Thanks,

Jennell

Do you ever use inline tables in your Qlik Sense app?  I do, especially when I need to add a small amount of data to an app that does not already exist in an Excel file or some other data source.  With the November 2017 release of Qlik Sense, manual entry was added in the Data manager.  With manual entry, users have the option to either manually enter their data into the table editor or paste their data into the table editor to add it to the Data manager.  I like this new small feature because it makes it very easy to copy Excel data or a table from a web page and add it as a table in the Data manager.  Let’s look at how it works.

 

From the Navigation menu, click on Data manager to open it.

menu.png

Once the Data manager is open, click on the plus sign to add data.  On the left, there is an option for Manual entry as seen below.  Select Manual entry to open the Manual entry editor window.  Once the Manual entry editor window is open, users can enter a name for the table they would like to add as well as the field names and table entries.  To enter data, double-click on the cell and start typing.  You will find that as you enter data into the cells, additional rows and columns are added automatically.  Once you are done entering the data for the table, click the Add data button at the bottom right to add the table to the Data manager.  Users can also paste in rows from an Excel table or a webpage which is how I like to use it.  Simply, copy the rows you would like to add, select the entire data entry table by clicking a column or row until they turn orange (as seen below) and then paste the table/rows (Ctrl-P).

manual entry paste.png

After the data is added to the Data manager, it can be loaded and added to the data model.  Refer to Qlik Sense Help for shortcuts that can be used when using the table editor.  Now, if you are accustomed to creating inline tables via the script editor, you can still do that in the script editor using the syntax below:

 

inline.png

 

 

 

This script will add a Products table with 2 fields, Product and Sales.  Five rows of data will be added to the Products table.

 

 

 

 

 

Manual entry allows users to manually enter data into the Data manager in a few easy steps.  Users do not need to remember the inline load script and they can load just the rows of data they need.  Manual entry is ideal when you have a small amount of data to add to an app.  When you have a lot of data, loading directly from the source or an Excel file may be easier.

 

Thanks,

Jennell

Jennell McIntire

Qlik Sense Histogram

Posted by Jennell McIntire Jan 12, 2018

What is a histogram?  A histogram is a chart that allows you to visualize the distribution of data over a continuous interval or certain period.  It is made up of bars like a bar chart but instead of displaying actual counts against a dimension, a histogram displays the frequency at each interval or bin allowing you to see where the values are concentrated and where there are gaps or odd values.  A histogram is also different from a bar chart in that it does not need measures.  Only a single numeric dimension is needed to create a histogram.  Once the dimension is added to the Qlik Sense histogram chart, the frequency is automatically calculated.

 

Let’s look at an example, in the histogram below, the dimension BMI (body mass index) is used to view the frequency.  After BMI was added to the chart, Qlik Sense automatically created the BMI bins based on the frequency distribution.  This chart shows that BMI is concentrated in the range 21 <= x < 22.75 (21 to 22.75, but not including 22.75).

histogram.png

The Qlik Sense histogram chart has some properties that can be used to customize the histogram.  There is the ability to set the number of bars to a maximum number or set the width of the bars if you would like the bars to be even intervals.  Qlik Sense Help has an example of a histogram using even intervals to show temperature.

num of bars.png               bar width.png

 

In the histogram above, the number of bars is set to 10.  When the bars are set to Auto, Sturges’ formula is used to determine the bins and this may change based on selections.

 

A histogram chart is ideal for a large amount of data when you need a quick visualization of frequency.  It is easy to create since only one dimension is required.  The key to remember is that the dimension field must be numeric.  Try it out with your numeric dimensions in your next Qlik Sense app.  If you are using QlikView, you may be interested in Henric Cronström’s blog Recipe for a Histogram on how to create a histogram in QlikView.  Also, check out Arturo Munoz’s blogs on the Distribution Plot chart and the Box Plot chart.  You may also find these helpful in your next Qlik Sense app.

 

Thanks,

Jennell

Today I thought I would share how I used the system functions OSUser() and DocumentName() in a Qlik Sense app.  Here is some background on the app - the app resides on a server and users must log in to access it.  Users can have multiple copies of the app and each file name is unique.  The app allows users to store the results of their analysis in QVDs and I wanted the QVDs to include the QVF name/id as well as the logged in user name to ensure that there is one set of QVDs per app.  To do this, I used the OSUser() and DocumentName() functions.


Let’s learn a little more about these functions.  According to Qlik Sense Help, OSUser() returns the name of the user currently connected and DocumentName() returns the name of the current Qlik Sense app.  In the script below, I created variables that store the results of these functions.

script.png

OSUser() returns a string formatted like this:

UserDirectory=xxx;UserId=xxx

 

This is what I stored in the vFullUser variable.  Then in the vUser variable, I used the Right, Len and Index functions to capture just the UserId portion of the string.  DocumentName() returns the app ID for the QVF.  I use the vUser and vAppName variables later in the script when I am storing the data into QVDs.  Below is an example of what that script looks like.  I append the user ID and the app ID to the name of the QVD.

store.png

Qlik Sense offers many system functions that can be used in both the script and chart expressions to provide system, device and Qlik Sense app properties.  Here is a list of the available system functions:

 

AuthorDocumentTitleIsPartialReload
ClientPlatformEngineVersionOSUser
ComputerNameGetCollationLocaleProductVersion
DocumentNameGetObjectFieldReloadTime
DocumentPathGetRegistryStringStateName

 

You can learn more about the system functions available in Qlik Sense Help.  The OSUser() and DocumentName() functions are easy to use and capture the information I need to make the QVDs specific to a user and an app.  Keep these functions in mind the next time you need information about a Qlik Sense app or the system/device it is running on.

 

Thanks,

Jennell

Jennell McIntire

Do..Loop

Posted by Jennell McIntire Nov 17, 2017

The do..loop control statement is used in the script to iterate through one or more statements until a condition is met.  It is useful when you need to dynamically check a condition while executing statements in the script.  There are two ways the do..loop can be used.  The first is to check the condition before executing statement(s) in the loop and the second is to check the condition after the statement(s) in the loop have been executed.  Let’s look at some examples.

 

The script below checks to see if the condition has been met before the statements are executed.  When x is no longer less than 10, the do..loop will stop.

Do while.png

The script below is a little different.  In this script, the condition is checked after the statements are executed so the statements will always run at least one time.

Do loop while.png

Both scripts produce the same results as seen below.  Products 1 through 9 were created.

Do while table.png

The do..loop also can be used with the “Until” keyword instead of the “While” keyword producing different results.  In the script below, the statements will be executed until x is less than 10.  Since x is equal to 1 which is less than 10, the statements never run so no products are generated.

Do until.png

Now if the condition is checked after the statements are run, as shown in the script below, one product will be created since the statements run once before the condition is checked.

Do loop until.pngDo until table.png

Now what if you need to exit the do..loop.  You can do this using Exit do.  The loop will be exited and execution will continue with the statement(s) following the loop.  Here is an example of what this looks like.  In the script below, the loop will exit after the Load statement runs 3 times, stopping when x is equal to 3.

exit do when.png

In the script below, this loop will exit after the Load statement executes one time.  Since x is not equal to 3, the loop will be exited when execution gets to the exit do statement.

exit do unless.png

The do..loop is helpful in the script when you need to execute statements while /until a condition is met.   It provides flexibility since the condition can be dynamic and be evaluated before or after the statement(s) in the loop.  This is a little different from the For..Next loop where a counter is used to determine the number of times the statement(s) in the loop are executed.  Learn more about loops in Henric’s blog Loops in the Script.

 

Thanks,

Jennell

Jennell McIntire

Dimension Colors

Posted by Jennell McIntire Oct 20, 2017

A few months ago, I wrote a blog about setting colors in master dimensions and measures.  Today I am going to share a little more about how master dimensions can be set to a single color or multiple colors.  Let’s first talk about setting a dimension to a single color.  This can be helpful when you want to use a color other than the Qlik Sense default color in your charts.  For instance, a bar chart may look like this with the default blue color.

default blue.png

But if your app is using a different color theme, you may want all the bars to be purple for instance.  You can do this by setting the Dimension color on the Edit dimension tab of the master dimension to the color you would like the dimension to be.  This color will be the same for all dimension values which is good practice when there are many dimension values.

edit dimension small.pngpurple small.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now let's discuss setting a master dimension to multiple colors.  Sometimes you may need the color for each dimension value to be different.  This is when you can use the Value colors tab of the master dimension to set the colors of each dimension value.  The stacked bar chart below is a good example of when you may want the dimension values to have different colors.  This chart is looking at the calls by activity type and call type.  The call type dimension values are set to 3 different shades of blue so that they can be distinguished in the stacked bar chart.  If the color values were not set, then the standard blue, red and yellow colors would be used instead.

 

stacked small.png

The color values can be set on the Value colors tab of the master dimension (see image below).  A color scheme can be used to set the colors or each value can be set manually.  The call type field only has 3 values so it is a good candidate for value colors.  You want to avoid using values colors or coloring by dimension if there are several different dimension values.  Too many different colors (or not so different colors) can become confusing and indistinguishable to the user.

value colors.png

 

On the Value colors tab, there is also the option to select a color for Others, if you are adding any limitations to the dimension, or for Null values.  When using the value colors to color your dimensions, be sure to select color by dimension and enable library color in the properties of the chart.  Check out this video in Qlik Sense Help for more information.

 

These color features available in dimension master items provide one place to set and edit the dimension colors used in charts.  You can set it once and use it in multiple charts.  This promotes consistency in our apps thus making them easier to digest and understand.

 

Thanks,

Jennell

Jennell McIntire

FileSize Function

Posted by Jennell McIntire Sep 22, 2017

In the past, I have blogged about the FileName function and how I used it to generate data for my Qlik Sense app.  Today, I thought I would blog about another file function, the FileSize function.  The FileSize function is a script function that can be used to return the size of a file or table.  The FileSize function returns an integer for the size, in bytes, of the file specified or a table.  The file can be a QVD, a text file, an Excel file or a table.  The syntax is quite simple:

 

Syntax:

FileSize([filename])

 

I have used the FileSize function to determine if a QVD exists before I load it.  In the example script below, I set the size of the CustomerMaster QVD to the vSize variable.  Then I check the variable to see if a size was returned.  If a size was returned meaning a QVD is there, I load the QVD.  If the QVD does not exist then the FileSize function will return null.

script.png

The FileSize function can also be used to return the table size of the table file being read.  To do this, the FileSize function is used in the Load statement as seen in the example script below.  When the filename parameter is excluded, then the FileSize function will return the size of the table currently being read.  The TableSize field will store the size of the CustomerMaster table after it has been loaded.

script2.png

The FileSize function is another easy-to-use script file function that can be used in Qlik Sense or QlikView to provide the size of a file or a table.  Use the FileSize function as a checkpoint in your script to ensure the file is available before loading, as I did in the example above, or use it ensure size requirements for files being loaded are being met in your app.  Good luck scripting!

 

Thanks,

Jennell

Qlik GeoAnalytics is known for its powerful mapping capabilities.  I had the opportunity to dig down into the layers of Qlik GeoAnalytics when working on a project about Indian Migration.  In this project, the team wanted to illustrate which states in India migrants moved to and left from.  The plan was to show this on a map.  Here were the requirements:

 

  • Use color to show the selected state (the state users move to or from)
  • Use color to show the number of migrants who moved
  • Use arrows to show the direction of the move
  • Place dots on each state that migrants move to or from

 

Here is a look at one of the resulting maps.  In order to build it, we used 3 GeoAnalytic layers: the Area layer, the Bubble layer and the Line layer.

Map.png

In the screenshot above, the selected state is Andhra Pradesh and it is highlighted in orange.  All the states that migrants came from are colored in a shade of green using the ColorMix1 function.  Darker shades of green indicate a larger number of migrants than the lighter shades of green.

 

Before we can begin to add the layers, we first had to add the GeoAnalytics Map to the app.  Once this is done, the layers can be added.  The first step was to use the Area layer to show which states users left from.  The color expression (which you can see in the attached app) first checked for the selected state and colored it orange, then it checked for states where no one migrated from and colored them gray.  The last part was to use the ColorMix1 function to color the states in shades of green based on the number of people who migrated from that state.  The second step was to add dots or bubbles to the state.  To do this the Bubble layer was used.  The third step was to add the Line layer to illustrate the direction of the move.  In the screenshot above, the arrows are all leading to the selected state, Andhra Pradesh.  The width and size of the arrows are based on the number of migrants.  The final few steps included using the Draw Order Adjustment property to indicate the order that we wanted the layers drawn – first the Area layer, then the Bubble layer and last the Line layer.  We also made use of the Info Bubble so that we could provide informative data when users hover over an area, line or bubble.

 

Feel free to check out the attached app using Qlik Sense to see how all the pieces were put together.  If you do not have Qlik Sense, you can download and try Qlik Sense Desktop for free from here.  In this project, we only used 3 of the layers available with Qlik GeoAnalytics.  There are other layers available as well as advanced geospatial information and geographic relationships.  Learn more about Qlik GeoAnalytics and check out our Getting Started Videos.

 

Thanks,

Jennell

One of the new enhancements in the June 2017 release of Qlik Sense is the ability to define colors in dimensions and measures in the master items library.  When creating a dimension or measure in master items, users now can specify a color that will be used for the dimension or measure.  This makes it easier for users to be consistent in their color choices throughout their app.  Every time the master item is used, the specified color will also be used.

 

I found this new feature helpful when building out visualizations for an app.  The design required that measures that showed the actual number be a rusty color while standard measures appeared as gray.  I could do this by selecting the color I wanted to use for each measure when I created the master item.  Let’s take a closer look at how I added the coloring in the objects below.

 

charts.png

 

The 3 KPIs on the left are measures.  In each one, I specified the color when I created the measure.  In the image below, you can see the new color option when you create/edit a dimension or measure.

edit measure.png

 

When I use the measure in a KPI, the color is automatically set since I specified it in the master item.  In the Cost Variance KPI at the top, I wanted to add conditional coloring instead of the color I specified in the measure so I selected the option to not use the library in this object as seen below.  This flexibility allowed me to take advantage of the conditional coloring when needed.  Note: The ability to turn off “Use library” is not available in all objects.

 

Color.png

 

In the combo chart, the bars and lines were colored based on the measure color giving me the ability to color the bars and line in the chart exactly as the design required.  While this may seem small, it is a huge enhancement for users and expands the design capabilities of Qlik Sense.  The color palette also provides several different color options so apps can be branded as needed.  To learn what else is new in Qlik Sense June 2017, check out Michael Tarallo’s blog.

 

Thanks,

Jennell

Jennell McIntire

Rank Function

Posted by Jennell McIntire Jun 30, 2017

Over the years, I have seen the Rank function used in various expressions.  Recently, I was working on a Broadway Shows project where we wanted to show the rank in a bar chart before the label adding a numeric indicator of how a show ranked.  You can see what I am referring to in the bar charts used in the Broadway app.  The bar chart below is an example of one of the charts.  We added the rank before the show name on the y-axis label.

Broadway.png

In this blog, I will discuss how the Rank arguments can be used to change how the rank is presented.  To do so, I will use a simplified data set to illustrate how the rank can be displayed.  Let’s start by defining the Rank function.  The Rank function is a chart function and is defined in Qlik Sense Help as:

 

Rank() evaluates the rows of the chart in the expression, and for each row, displays the relative position of the value of the dimension evaluated in the expression. When evaluating the expression, the function compares the result with the result of the other rows containing the current column segment and returns the ranking of the current row within the segment.

 

For charts other than tables, the current column segment is defined as it appears in the chart's straight table equivalent.

 

Syntax:

Rank([TOTAL] expr[, mode[, fmt]])

 

Originally, I used the Rank function without any arguments.  This will rank the products by their sales and display it before the product label.

Rank no arguments.png

Rank no arguments chart.png

By default, with no arguments for mode and fmt, the Rank function will show the lowest and highest value displayed like a range as seen in the chart above.  But what if you want to show a single number for the rank where Product_B and Product_J would show 1 as the rank for both projects since they both tied for first place.  This can be accomplished by using the mode and fmt arguments.  Mode can take values 0 – 4 and controls which rank is shown for each row for example show the lowest rank, the average rank or the highest rank.  The fmt argument takes values 0 – 2 and controls how and where the rank is displayed.  You can find the detailed description for each setting in Qlik Sense Help.

 

For the Broadway app, I set the mode to 4 and the fmt to 1.  If I do this with this data set, I will get a chart like this:

Rank arguments.png

Rank arguments chart.png

The mode argument is 4. This will show the lowest rank on the first row, then increment by one for each row.  The fmt argument is 1 and this will show the low value on all rows.  When there is a tie, only the lowest rank will be displayed on the respective rows.

 

There are not always ties when using the Rank function but when there are, the Rank function provides options for how to handle the display of the rank.  This was something I never had a need for until now but I found it to be very valuable so I thought I would share what I have learned.  The next time you use the Rank function, try out the various arguments to find the display that works best for your app.

 

Thanks,

Jennell

The switch control statement is one of many controls statement that can used to determine the flow of the script.  In Qlik Sense help, it is defined as:

 

The switch control statement is a script selection construct forcing the script execution to follow different paths, depending on the value of an expression.

 

Syntax:

Switch expression {case valuelist [ statements ]} [default statements] end switch


The switch control statement can be used in the script to identify a specific execution path.  With the switch control statement, several paths can be defined and a default path can be defined as well when no match is found in the case clause.  The various script paths cannot cross – they should be individual paths that do not overlap.  In the script below, the expression x will be compared to each case.  When there is a match, the script for that case will be executed.

 

In the example below, Case 1 will be executed – the variable y will be set to Sunday and one record will be generated in the table.

script.png

Below is a look at the record generated.

Table.png

It is also possible to have more than one value for a case, for instance, you can use the script below to run the same script if x matches 1, 2 or 3.

case 1 2 3.png

When faced with various script execution paths, try using the switch control statement.  While I rarely have a need to use it, I like how neat and straight-forward the syntax is.  I have attached an example Qlik Sense app with the full script for you to test out for yourself.

 

Thanks,

Jennell

A couple of years ago I wrote a blog on customizing straight tables in QlikView explaining how you can add an ad-hoc report to your QlikView app.  So, I thought I would share how you can now create a custom report in Qlik Sense using the Climber Custom Report extension.  The Climber Custom Report is an extension that can be added to your Qlik Sense app to give users the ability to create their own ad-hoc reports.  In this blog, I will show you how easy it is to add a custom report to your app using Qlik Sense Desktop.

 

  1. The first step is to download the Climber Custom Report extension from Qlik Branch, unzip it and put it in your Extensions folder (C:\Users\xxx\Documents\Qlik\Sense\Extensions).
  2. In your Qlik Sense app, create a table with all the possible dimensions and measures a user may want to see in a report and then add the table to master items.  The table may look something like this: table.png
  3. Add the Climber Custom Report extension to a sheet in your app and then you are ready to create a report.
  4. In the Visualizations drop down, select the report you just created.  All tables that are in your visualization master items will be listed in the drop down.  Once the table is selected, the dimension and measure lists will be populated with the dimensions and measures that are used in the table as seen below.dimensions and measures.png
  5. Select the dimensions and measures you would like to add to your report by clicking on them.  Your report will look something like the image below after you make some selections.  The dimensions are blue and the measures are orange.selections.png
  6. From the custom report bar above the chart, you can remove a dimension or measure by clicking the x and you can change the order of the columns by dragging and dropping the dimensions and measures into the order you would like them to appear in the report.

 

And that is it - it is that simple to add a custom report to your Qlik Sense app.  With Qlik Sense self-service, a user can create a report by dragging and dropping dimensions and measures into a table but what I like about the Climber Custom Report extension is it makes everything available to the user with a clean, professional and organized look.  Download it now and test it out for yourself.  See the extension in action in the Situational Awareness demo.  Note - the Climber Custom Report extension works in Qlik Sense 3.0 and higher and, like all extensions, are not supported by Qlik.

 

Thanks,

Jennell

Filter Blog

By date:
By tag: