Epic and Related issues:-

I am working to develop the dashboard with Jira in qliksense . One of the task is to analyse Epic and its related issues . although Epic itself a Issue but if you see the Epic in Jira tool you will see Epic has many issues related with it and you have to find out the total spent hours on selection of the Epic issue which shows total hours spent of all the issues which are related to Epic.

To achieve this requirement we have to develop a hierarchy with Jira issue and Project table .

Below tables I have used to achieve this

• Jira Issue
• Issuelink
• IssueType
• Project
• Issuetype

Step1 - Load Issue Table with fields which you required also perform the apply map to fetch the issue type from issuetype table and Project key from Project.

mJiraIssueType:

Mapping LOAD

                ID,

     pname as issuetype

FROM

[$(QVDExtractPath)/JIRA_issuetype.qvd]

(qvd);

//******************************** Below code to fetch the Project Key

ProjectKeyEpic:

mapping LOAD

               ID AS %ProjectID,  

    pkey AS %ProjectKey

FROM

[$(QVDExtractPath)/JIRA_project.qvd]

(qvd);

Step2 – Load issuelink table and perform apply map to fetch the field linkname (which identify the issue type is epic , cause etc) from table linktype ( You can also perform inner join between issue link and linktype table on the basis of ID in linktype and LINKTYPE field in issuelink table)

Please check below

LOAD   

    LINKTYPE,

    SOURCE as %IssueID,

    DESTINATION,

    ApplyMap('LinkName',LINKTYPE ,'Unknown') as LINKNAME,

    ApplyMap('JiraProject',DESTINATION,'') as %ProjectID  // Based on actual Issue in Jira issue link qvd   

FROM [lib://SenseData/1.QVD/1.Extract/JIRA_issuelink.qvd]

(qvd) ;

Step3 - Also fetch Project id from project table on the basis of DESTINATION in issuelink and issueid (ID) in Jira Issue table . we need the project id later .

Step4 - Perform inner join between Issue and  issuelink table based on issueID (ID in Jira Issue table) and Source in issuelink table . it will gives you all the issues related to Epic .

Step5 -  Perform inner join again with resultant table (Issue and Issuelink) with Jira issue table to find out issue summary of all the related Issues in Epic and  Epic issue num.

Please check the code below

Inner Join(IssueLink)

LOAD

    ID as DESTINATION,

    issuenum as EpicIssues,

    SUMMARY as ISSUES_IN_EPIC   

FROM [lib://SenseData/1.QVD/1.Extract/JIRA_jiraissue.qvd]

(qvd);

Step6 – Load the project table to fetch the project name and Issuekey of the Epic and related projects

TableTmp:

Load

               ID AS %ProjectID,

               pkey AS %ProjectKey

FROM

[$(QVDExtractPath)/JIRA_project.qvd]

(qvd);

Left Join(TableTmp)

Load

               %ProjectID,

    %IssueID,

    %IssueKey,   

    ProjectKeyEpic,

    IssueType

Resident JiraIssue_Temp;

Left Join (JiraIssue_Temp)

Load

    %IssueID,

    %ProjectKey as  %ProjectKey_Temp,   

    if(WildMatch(IssueType,'Epic'),Upper(ProjectKeyEpic&'-'&%IssueKey),Upper(%ProjectKey&'-'&%IssueKey)) as %ProjectJiraIssueKey

Resident TableTmp;

Drop Table TableTmp;

The if condition below which is used above  highlight in bold is used to identify  the Full Issue ID (Key+Isueid) of Epic and its related issues. Please provide any name, in my case I give the name %ProjectJiraIssueKey.

"if(WildMatch(IssueType,'Epic'),Upper(ProjectKeyEpic&'-'&%IssueKey),Upper(%ProjectKey&'-'&%IssueKey)) as %ProjectJiraIssueKey"

Congrats after this step you have all the required information related to all the issues which are related to Epic .

If you don’t want to add the time which are in Epic issue then this is the last step but if you want to summing up the time associated to Epic issue itself then perform the below step .

Step7- Resident the above code which we have developed to associate the issues with Epic and concatenate the Epic issue with code below and create the QVD with Name Jira_Epic

JiraEpic:

Load *, Upper(%ProjectKey_Temp &'-'& EpicIssues) as %ProjectIssueKey , 'Jira' as Flag Resident JiraIssue_Temp ;

//******************************** Below concatination is used to add Epic Issue into the

Jiralist ********************

Concatenate

JiraEpic:

Load %IssueID,IssueType,

        IssueSummary ,

      %ProjectJiraIssueKey  ,

      Upper(ProjectKeyEpic&'-'&%IssueKey) as %ProjectIssueKey,

      %ProjectJiraIssueKey as %ProjectEpicKey ,     

      'Epic' as Flag

 Resident JiraIssue_Temp where match(IssueType,'Epic');

Drop Table JiraIssue_Temp;

Sorry for the grammatical mistake and Hope this will help you in  analyse the Epic and related issues .

Thanks and Happy Qliking

Ajay

Five years ago, I released RightQlik as a free utility for adding a context menu in Windows Explorer that provides easy access to common operations on QlikView (QVW) files. After thousands of downloads from around the world, and after using it myself almost daily, it has proved to be quite handy and super helpful, saving me and fellow developers a lot of clicking when working with QlikView documents.

Today, I’m pleased to announce the release of RightQlik 2.0, now with support for Qlik Sense (QVF) files. This version of RightQlik integrates with the excellent open source tool corectl (which is built by the Qlik Core team) to provide easy access to advanced operations on QVF files like viewing metadata, exporting scripts and reloading the app with a single click.

When installing RightQlik, it will also install the latest version of corectl automatically. Since corectl interacts directly with the Qlik Analytics Engine, you need to have Qlik Sense Desktop installed and running in order to take advantage of these operations.

RightQlik 2.0 also provides quick access to explore the app’s data model in Catwalk, another excellent open source tool released by Qlik.

Here’s a quick glance at the options available from the Windows Explorer context menu once you install the new version of RightQlik:

And here’s a quick demo video of the various operations you will have access to:

Download now! It's free

And, of course, reach out if you have any comments or ideas to make this tool better!

Objective

Sending parameters from a scheduled app to its script.

Summary

The post shares the approach used to send parameters from the app name to its script. The referred approach only makes sense if your source code is externalised in an include. Otherwise, the script will be duplicated, and definitely this would not be a sustainable approach.

Use Case

Massive data needed to be reloaded as fast as possible in order to allow the business to proceed with month-end routines. Time was the most critical aspect when this approach was developed.

Each day had between 70 and 80 million rows and the last three months needed to be reloaded every day to capture changes. The incremental approach was used to scan those three months to capture only the rows changed.

Solution

Concurrent reloads would reduce the total reload time.

Challenge 1 – How to reload several apps without duplicating the app script?

It is known that up to now (June 2020), Qlik Sense does not provide any way to send parameters from a task to the script, as QlikView does.

Here the main question is raised: How can I send a parameter from a scheduled app to its script? The answer is simple, the app name is the key. The app name is the source for your parameters and the post shares the approach developed.

Challenge 2 – Concurrent reloads can drain the resources from the server.

When you put in place concurrent reloads the queues will be taken by those apps and will be released only when the reload is completed. Each case must be evaluated to balance apps and server resources. Although, there is no “recipe”, some aspects should be considered such as if the environment has near real-time apps they should be moved to a dedicated scheduler. If that is not taken into consideration, the “near real-time concept” will be lost because the reloads won’t perform as expected.

Another point to be considered is that increasing the number of queues won’t solve the problem if the server resources remain the same.

The Approach

The most important thing to be defined and kept in mind is how the apps will be named to provide an easy way to recover the parameter name and parameter value from the name. I strongly recommend the use of delimiters. I decided to use “_” (underscore) once it keeps the app name readable and the parameters can be easily identified.

Example 1:

APP: EXT_FACT_TDW_DBO_TINTERNAL_TRANSACTION_MONTH_00_FROM_DAY_01_TO_DAY_05

App name: EXT_FACT_TDW_DBO_TINTERNAL_TRANSACTION

Parameter Name 1: MONTH_

Parameter Value 1: 00

Parameter Name 2: FROM_DAY_

Parameter Value 2: 01

Parameter Name 3: TO_DAY_

Parameter Value 3: 05

To get the app name in the script, use the DocumentTitle() function and then apply the necessary logic to retrieve the parameter names and parameter values from the app name. Keep it simple, make the logic reusable, define parameter names, and parameter values explicitly, no one must have to guess what the app name contains as parameters.

From the app name, it can be guessed that the script reloads month 00 (current month) in batches of five days per app, which means six extractors running in parallel. So, to extract three months the EXT_FACT_TDW_DBO_TINTERNAL_TRANSACTION app must be duplicated and only the app name needs to be changed. This is how the whole set of apps is:

Attached is the full task chain to the end application and also the script for the extractor (open the docx file, copy, and paste the script in a text file, it is easier to read).

The approach is not failure-proof once it is based on the app name and any mistake done in the app name will cause the app to fail. However, checks can be put in place to prevent garbage in the parameters.

Note 1: Changes done in the script don’t need to be republished because the script is externalised in an include and you are not dealing with the QVF directly.

Note 2: The task chain is very sensitive, make sure that all dependencies were correctly set.

Note 3: Binary load is used in all end applications.

Conclusion

The target was successfully achieved, apps are reloaded faster, and business is able to start month-end earlier on the first day of the month compared to reports sent via e-mail.

An effective approach was created to send parameters from a scheduled app to its script, no duplicated code, easy to expand the concept to other months. What needs to be done is only duplicate the app for the month 04, rename accordingly, and schedule them. The script will take care of everything else.

As expected, the server resources are highly demanded when the task chain starts but it was able to handle all the tasks without errors. The virtual server has 384GB RAM and 16 vCPUs.

A small note goes to the scheduler server. By default, the timeout for queued tasks is 30 minutes. It means that when a task is triggered it waits for up to 30 minutes before it gets aborted automatically by the system. If your environment is configured to send e-mail alerts if a task fails, you might get some e-mails that you don’t want to receive. My recommendation is, before changing the parameter, make sure that everything else was done the guarantee the best performance possible. The Engine timeout can be changed here: QMC -> Schedulers -> select the scheduler server -> select Advanced (on the right) -> change the "Engine timeout (minutes)" parameter and save.

I will be more than pleased to go into details if I was not very clear in a specific aspect.

Comments and suggestions will be more than welcome.

I do hope this post can give you a new perspective to deal with massive data loads, time constraints, and improve the flexibility and adaptability of your Qlik Sense environment.

Huberto Pereira Haidemann

(Qlik Sense Server only, Qlik Sense Desktop has no Repository)

There are situations where you would like to know something from the system configuration (QMC) from inside of a load script, something like "Which custom attributes does my app have assigned?" Or "Which stream is the current app in?" I've seen the "bad" idea to connect to the Postgre repository database and read from the internal table structure. This internal structure may, and is likely to, change, so I would not build on this hack.

The correct way to do it is to use the QRS API (Qlik Repository Service). In theory, the QRS API operates on the https port 443 as well as on port 4242. If you used the latter, you have to present the public key of the server certificate, which cannot be done from the load script (remark added 2020: the REST Connector meanwhile allows to add a Certificate File and Key File, so you can setup a connection directly to port 4242 when allowed from a firewall point of view).

In order to use the port 443, you need to authenticate as a privileged user towards the server - your best bet from the script is to use the header authentication (pass the userid via http-header) and this requrires the setup of a Virtual Proxy on the server by a QMC admin.

1. Setup a new Virtual Proxy with Header Authentication
2. Use Postman or another tool to test the qrs endpoint response before going to the load script
3. Load Script: Use REST Connector with CONNECTION parameter

Step 1 - Setup Virtual Proxy

We want the script to be able to query the QRS api, so we have to give it a new "door" to authenicate. Set up a new Virtual Proxy like this: There will be a static pseudo user directory which we call "APIUSERS", whereas the user id itself will be provided in a http-header.

Dont forget to

• add the Server node(s) under "Load balancing"
• link this virtual proxy to a Proxy in the "Associated Items".

Note: We will have to come back to QMC/Users once again after making the first call using the header.

Step 2 - Test the qrs endpoint with header authentication

It is literally impossible to go straight to the REST connector from the script if you haven't tried the results from a tool that shows all response details (return code, possible error messages etc). My tool of choice is "Postman" (the app, not the Chrome-Plugin of the same name)

Lets use the server url + /header/ (this is the virtual proxy we created) + /qrs/about + &xrfkey= and a 16 digit combination, which you repeat in the http-headers under the key "X-Qlik-xrfkey" again. Also provide the userid as a 2nd http-header

You should get an answer like above in Json format. This means, you made it through the virtual proxy. A new user has been added to the QMC, namely the id "scriptload" (coming from the http-header) with the directory "APIUSERS" (coming from the virtual proxy setting).

Go back to the QMC and provide the new user the Role "AuditAdmin"

Now you can also query some details about the app. Try the following request, before we finally go to the Load Script. -.../qrs/app/full should provide all information about apps, including possible tags.

In order to get the details about one specific app you should use the "filter=" query-string, the syntax is like the Json object key, a comparison operator, and a constant. In my case: id eq <app-guid>

eq is "equals", don't use "=" which is a reserved character already used at "filter="

If this works fine, lets edit the app script.

3 - Create REST Connector call from Load Script

Go to the app's dataload editor and create a new connection of type "REST".

The principle I follow here is:

1. get over the initial dialog with hard-coded query-strings and http-headers to get the qrs call working (same Method as later, GET for GET, POST for POST)
2. load the data once
3. manipulate the created script with "WITH CONNECTION" argument and soft-code all the parameters to make it dynamic

Then load the data using the "Select Data" button in the Data connections list:

You can expand the Json response tree and deselect what you don't need. Be aware that each tree branch, which expands, will become a separate script table, so be conservative with what to load using a "need to know" judgement.

Insert the script. You will see the block called "RestConnectorMasterTable: SQL SELECT ..." and then several LOAD statements (in our case 4, one for the root and 3 childs which we selected above)

Until this point, the script is fully static. It only loads exactly this QRS endpoint and always load all apps.

Now lets bring some dynamics in. Find the last row of the first block where it reads FROM JSON (...) "root" PK "__KEY_root";

Before the semicolon insert (in a new line) WITH CONNECTION ()

With this parameter you can soft-code and overwrite any hard-coded entry from the "Edit Connection" dialog. If you omit a parameter in WITH CONNECTION, the original hard-coded entry from the Edit Connection dialog will be taken. You can also add more parameters using WITH CONNECTION. Use the WITH CONNECTION script as follows:

• If you want to overrule the endpoint address use URL "https://...." option
• If you want to add another Query-string (filter for example) use QUERY "filter" "id eq cde61c53-b536-46f3-8538-6470042e4160" (QUERY takes 2 more parameters, key and value)
• If you want to add another header-key use HTTPHEADER
• Note: You can NOT change the method (GET/POST), you need another data connection if you have to use the other

Here is my example of getting the info just for the current app:

Add this row before the "LIB CONNECT TO ..." statement:

LET vAppId = DocumentName();

This will assign the app GUID to the variable.

Modify the last row of the first block as follows:

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION (

URL "https://qmi-qs-aai/header/qrs/app/full"

    ,QUERY "filter" "id eq $(vAppId)" 

);

Note: the variable $(AppId) is slided into the filter. QUERY xrfkey;

When you execute the script, you will get the app info from the QRS API loaded as a table datamodel:

Now you can use some of your scripting skills to get, for example, to check which tags the given app has and do something:

FOR v = 1 TO FieldValueCount('name_u0')

    LET vTag = FieldValue('name_u0', 1);

    IF vTag LIKE 'byCSW' THEN

        TRACE --- this app has the tag of the tags ---;

    END IF

NEXT v

More Advanced Script Example or QRS API

In the following example I am loading the list of all tasks for a given app. For the tasks I also want a 1:n-List of Custom Properties (if any) and Tags (if any). The returned Json structure would be complex (8 tables for the qrs endpoint /qrs/reloadtask/full):

Deselect what you don't need. I also unchecked a lot of unneeded columns. You better spend a few minutes in the "select data" dialog of the Data Connector than in the script below.  To see which fields I skipped I commented them out in the code below. I also introduced "nice field names" like "tag.name" or "task.id" instead of auto-generated names such as "name_u4", "id_u6" ...

Finally, I joined some tables to have only 3 relevant ones after this.

Here is the sample script:

LET vAppId = DocumentName(); LET vXrfkey = Left(KeepChar(Repeat(Rand(),10),'0123456789'), 16); TRACE Onetime Xrfkey = $(vXrfkey);     LIB CONNECT TO 'QRS full app info (qmi-qs-aai_vagrant)';     RestConnectorMasterTable: SQL SELECT    "id" AS "id_u6", // "createdDate" AS "createdDate_u0", // "modifiedDate" AS "modifiedDate_u0", // "modifiedByUserName" AS "modifiedByUserName_u0", // "isManuallyTriggered",    "name" AS "name_u2",    "taskType",    "enabled", // "taskSessionTimeout", // "maxRetries", // "privileges" AS "privileges_u5", // "schemaPath" AS "schemaPath_u0",    "__KEY_root",    (SELECT    // "id" AS "id_u0",    // "createdDate",    // "modifiedDate",    // "modifiedByUserName",       "value",    // "schemaPath",       "__KEY_customProperties",       "__FK_customProperties",       (SELECT       // "id",          "name",       // "valueType",       // "privileges",          "__KEY_definition",          "__FK_definition"       FROM "definition" PK "__KEY_definition" FK "__FK_definition")    FROM "customProperties" PK "__KEY_customProperties" FK "__FK_customProperties"),    (SELECT       "id" AS "id_u1",       "name" AS "name_u0",    // "appId",    // "publishTime",    // "published",    // "stream",    // "savedInProductVersion",    // "migrationHash",    // "availabilityStatus",    // "privileges" AS "privileges_u0",       "__FK_app"    FROM "app" FK "__FK_app"),    (SELECT    // "id" AS "id_u5",       "name" AS "name_u1",    // "privileges" AS "privileges_u4",       "__FK_tags"     FROM "tags" FK "__FK_tags") FROM JSON (wrap on) "root" PK "__KEY_root" WITH CONNECTION (    URL "https://qmi-qs-aai/header/qrs/reloadtask/full"    ,QUERY "filter" "app.id eq $(vAppId)"    ,QUERY "xrfkey" "$(vXrfkey)"    ,HTTPHEADER "X-Qlik-Xrfkey" "$(vXrfkey)" // ,HTTPHEADER "userid" "scriptload" );         [root]: LOAD    [id_u6] AS [task.id], // [createdDate_u0] AS [createdDate_u0], // [modifiedDate_u0] AS [modifiedDate_u0], // [modifiedByUserName_u0] AS [modifiedByUserName_u0], // [isManuallyTriggered] AS [isManuallyTriggered],    [name_u2] AS [task.name],    [taskType] AS [task.taskType],    [enabled] AS [task.enabled], // [taskSessionTimeout] AS [taskSessionTimeout], // [maxRetries] AS [maxRetries], // [privileges_u5] AS [privileges_u5], // [schemaPath_u0] AS [schemaPath_u0],    [__KEY_root] AS [__KEY_root] RESIDENT RestConnectorMasterTable WHERE NOT IsNull([__KEY_root]);     LEFT JOIN //[app]: LOAD    [id_u1] AS [app.id],    [name_u0] AS [app.name], // [appId] AS [appId], // [publishTime] AS [publishTime], // [published] AS [published], // [stream] AS [stream], // [savedInProductVersion] AS [savedInProductVersion], // [migrationHash] AS [migrationHash], // [availabilityStatus] AS [availabilityStatus], // [privileges_u0] AS [privileges_u0],    [__FK_app] AS [__KEY_root] RESIDENT RestConnectorMasterTable WHERE NOT IsNull([__FK_app]);     [customProperties]: LOAD // [id_u0] AS [id_u0], // [createdDate] AS [createdDate], // [modifiedDate] AS [modifiedDate], // [modifiedByUserName] AS [modifiedByUserName],    [value] AS [customProp.value], // [schemaPath] AS [schemaPath],    [__KEY_customProperties] AS [__KEY_customProperties],    [__FK_customProperties] AS [__KEY_root] RESIDENT RestConnectorMasterTable WHERE NOT IsNull([__FK_customProperties]);     LEFT JOIN //[definition]: LOAD // [id] AS [id],    [name] AS [customProp.name], // [valueType] AS [valueType], // [privileges] AS [privileges],    [__KEY_definition] AS [__KEY_definition],    [__FK_definition] AS [__KEY_customProperties] RESIDENT RestConnectorMasterTable WHERE NOT IsNull([__FK_definition]);       [tags]: LOAD // [id_u5] AS [id_u5],    [name_u1] AS [tag.name], // [privileges_u4] AS [privileges_u4],    [__FK_tags] AS [__KEY_root] RESIDENT RestConnectorMasterTable WHERE NOT IsNull([__FK_tags]);       DROP TABLE RestConnectorMasterTable; DROP FIELDS [__KEY_definition], [__KEY_customProperties];

Objective:

For this document we will review how to POST data into a webhook using the Qlik Rest Connector. We will review two examples of calling a webhook in Slack and Microsoft Teams.

What is a webhook

A webhook is a configurable callback URL which allows for applications to call other applications. The most common use cases involving webhooks involve integrating alerting into chat applications like Slack or Microsoft Teams.

Qlik REST Connection Configuration

In this section we will configure a Data Connection using the Qlik REST Connector for a POST which will later be used by both the Slack and Microsoft Teams examples.

• Create a new Data Connection

• Select the REST Connector option

• Configure the Data Connection:
  • For the URL, enter an arbitrary REST endpoint which accepts POST requests. We are using an example from https://jsonplaceholder.typicode.com/ but you can adjust to fit your environment.
  • Select the Method of POST
  • Uncheck Check response type during 'Test Connection'

• Give the Data Connection a meaningful name & Save

Slack

Slack Pre-Requisites:

This section will be heavily adapted from Slack's API documentation. For a more exhaustive and ultimately up-to-date review, refer to Slack’s documentation for guidance.

Note: These steps may require elevated permissions on your Slack instance. If unavailable consult your Slack administrator(s) for guidance.

• Add the Incoming WebHooks app to your Slack instance

• Select the channel which you want to post to

• Scroll to the integration settings and
  • Copy the Webhook URL
  • (Optional) customize the name
  • (Optional) customize the icon / upload your own

• Save the settings

Qlik Load Script Configuration for Slack:
For this example, we will use more advanced features that are minimally required to post to Slack. We will format our message to include both a dynamic URL to the Qlik app which was reloaded as well as an icon to a meaningful image to symbolize our Qlik app. You can refer to the Slack documentation on its capabilities for more customization of the message.

We will use a load script similar to this one:

// Define your Slack Webhook URL
LET vSlackWebHook = 'https://hooks.slack.com/services/...';
// Build out the URL
LET vServerHost = 'https://qliksense.domain.ad/'; // use https://server.company.com/prefix/ if using an alternative virtual proxy
LET vPath = 'sense/app/'; // Path for direct opening of an app
LET vDocID = DocumentName(); // Determine the app's GUID
// Here is an example body
LET vBody = '{"blocks":[{"type":"section","text":{"type":"mrkdwn","text":"Qlik app reloaded \n <';
LET vBody = vBody & vServerHost  & vPath & vDocID & '|Click here to access>"},"accessory":{"type":"image","image_url":"https://i.imgur.com/vakMyWS.png","alt_text":"App Icon"}}]}';
// Replace the " characters with the chr representations
LET vBody = Replace(vBody,'"',chr(34) & chr(34));
// Replace the / characters with the chr representations
LET vBody = Replace(vBody,'/',chr(47));
// Replace the \ characters with the chr representations
LET vBody = Replace(vBody,'\',chr(92));
// Replace the * characters with the chr representations
LET vBody = Replace(vBody,'*',chr(42));

// This is just a generic REST connection to an endpoint which has the POST method configured
LIB CONNECT TO 'REST-POST (domain_admin)';

RestConnectorMasterTable:
SQL SELECT 
	"col_1"
FROM CSV (header off, delimiter ",", quote "'") "CSV_source"
	WITH CONNECTION (
    	URL "$(vSlackWebHook)",
    	BODY "$(vBody)"
);

[CSV_source]:
LOAD	[col_1]
RESIDENT RestConnectorMasterTable;


DROP TABLE RestConnectorMasterTable;
DROP TABLE [CSV_source];

You will want to adjust the following:

• vSlackWebHook: Insert your Webhook URL copied from the Incoming Webhook configuration
• vServerHost: Adjust to the URL which users will use to access Qlik. If you are using an alternative virtual proxy, then be sure to include this value as well.

After we build the body, you can see that we are replacing certain special characters with their chr representations since the Qlik REST Connector would otherwise treat them as literals. If you have other special characters in the body that you are constructing, then consult with a ASCII / CHR code mapping. This guide used this one (https://testguild.com/qtp-ascii-chr-code-chart/).

Once you have adjusted the body to suit your needs and adjusted the required parameters, reload the app.
If successful then you should receive an alert like so:

Microsoft Teams

Microsoft Teams Pre-Requisites:
This section will be heavily adapted from Microsoft Team’s Documentation. For a more exhaustive and ultimately up-to-date review, refer to Microsoft’s documentation for guidance.

Note: These steps may require elevated permissions for the Team. If unavailable consult the Team’s owner(s) for guidance.

• Go to the Connectors option for the channel

• Search for incoming webhook and add this connector

• Give the webhook a name and optionally customize the icon which it will use then create

• Scroll down after creation and copy the Webhook URL

Qlik Load Script Configuration for Microsoft Teams:
For this example, we will use more advanced features that are minimally required to post to Microsoft Teams. We will format our message to include both a dynamic URL to the Qlik app which was reloaded as well as a clickable icon for users to easily access the Qlik app. You can refer to the Microsoft Teams documentation on its capabilities for more customization of the message.

We will use a load script similar to this one:

// Define your Slack Webhook URL
LET vTeamsWebhook = 'https://outlook.office.com/webhook/....';
// Here is an example body
// Build out the URL
LET vServerHost = 'https://qliksense.domain.ad/'; // use https://server.company.com/prefix/ if using an alternative virtual proxy
LET vPath = 'sense/app/'; // Path for direct opening of an app
LET vDocID = DocumentName(); // Determine the app's GUID
LET vBody = '{"@context":"https://schema.org/extensions","@type":"MessageCard","themeColor":"0072C6","title":"Qlik Sense App Reloaded","text":"Click **Open** to access the app","potentialAction":[{"@type":"OpenUri","name":"Open","targets":[{"os":"default","uri":"';
LET vBody = vBody & vServerHost  & vPath & vDocID & '/overview"}]}]}'; // Dynamically build out the variables defined previously
// Replace the " characters with the chr representations
LET vBody = Replace(vBody,'"',chr(34) & chr(34));
// Replace the / characters with the chr representations
LET vBody = Replace(vBody,'/',chr(47));
// Replace the @ characters with the chr representations
LET vBody = Replace(vBody,'@',chr(64));
// Replace the * characters with the chr representations
LET vBody = Replace(vBody,'*',chr(42));

// This is just a generic REST connection to an endpoint which has the POST method configured
LIB CONNECT TO 'REST-POST (domain_admin)';

RestConnectorMasterTable:
SQL SELECT 
	"col_1"
FROM CSV (header off, delimiter ",", quote """") "CSV_source"

	WITH CONNECTION (
    	URL "$(vTeamsWebhook)",
    	BODY "$(vBody)"
);

[CSV_source]:
LOAD	[col_1]
RESIDENT RestConnectorMasterTable;


DROP TABLE RestConnectorMasterTable;
DROP TABLE CSV_source;

You will want to adjust the following:

• vTeamsWebhook: Insert your Webhook URL copied from the Incoming Webhook configuration
• vServerHost: Adjust to the URL which users will use to access Qlik. If you are using an alternative virtual proxy, then be sure to include this value as well.

After we build the body, you can see that we are replacing certain special characters with their chr representations since the Qlik REST Connector would otherwise treat them as literals. If you have other special characters in the body that you are constructing, then consult with a ASCII / CHR code mapping. This guide used this one (https://testguild.com/qtp-ascii-chr-code-chart/).

Once you have adjusted the body to suit your needs and adjusted the required parameters, reload the app.
If successful then you should receive an alert like so:

This document is about how to improve "Gauge" visualizations to better display both their values and thresholds, as well as considering direct indicators (0% is better) and inverse ones (100% is better) using the same visualization.

Starting point

In Qlik Sense applications we usually find “Gauge” type visualizations for KPIs, which typically represent the value compared to a couple of thresholds (Green & Red) to delimit the 3 sections that are represented when comparing with the goals: Green, Yellow and Red.

This way, they are usually represented in the following way in many applications:

For this case the thresholds are 80% and 90%.

This visualization is correct, but for sure we can make some improvements:

• Display of thresholds: The thresholds are not clearly displayed, and the user cannot know exactly how far they are from each one and from the current value.
• Axis scale: When the thresholds are very close to the limits (usually 0% or 100%), they are not appreciated since the proportion makes them appear very close and sometimes hard to know if it is green or yellow:

• Axis inversion: When the same indicator has inverted thresholds, the visualization leads to error. In the following example, the Red threshold is 90% and the Yellow threshold is 95%. This means that it should be represented from 0 to 90% in red, from 90% to 95% in yellow, and 95% to 100% in green. However, the user sees that it is Green when it should be Red:

Recommendations for improvement

This visualization, in my opinion, can be improved by implementing all or some of the following recommendations on its properties:

Displaying the thresholds

To show the thresholds in this visualization, and show the current value in the context of the goals, we can implement 4 improvements:

1. Use the "Footnote" to put the exact values of the thresholds. For example, with the following expression:

= 'Green: ' & Num(ThresholdGreen, '0,00%') & ' - Red: ' & Num(ThresholdRed, '0,00%')

1. Show thresholds as reference lines. Depending on the Qlik Sense version, the reference line label could not be dynamic, but it can be left blank as the value is shown by default:

• Green threshold:

= ThresholdGreen

• Red threshold:

= ThresholdRed

• Use colors in the thresholds to improve the interpretation, and thus associate each threshold with the color of the segment it defines. Optionally, if you want to consider users with poor color perception, you can use the threshold trend line labels to also put the segment text next to the threshold value.
• In the "Scale" property of the measure axis, in the Appearance properties, choose the option "Wide", this way there will be few values on the axis other than the maximum and maximum limits and the thresholds, thus making them easier to read.

The result would be the following:

Axis scale

To improve the visualization of the values when they are far from 0% or 100%, we can make both the maximum and the minimum of the axis variable depending on the thresholds and the indicator value. In this way, the maximum could be set a little above the value of the upper limit or the value (if greater than this limit), so as not to always have to show the scale up to 100%. For example, in the following cases a lot of space is wasted of the axis for red in the first case or for green in the second:

If we make the maximum value of the axis (and the minimum as well, although we will see this after reviewing the next section) to be dynamic based on the upper threshold and the value of the indicator, we can use the following expression that calculates it to be a 10% above the measurement value or upper threshold (rounded to the next ten percent above the limit to avoid decimals, using the Ceil function):

=If(ThresholdGreen < ThresholdRed,    If(Value < 0.9,        If(Value > ThresholdRed,            Ceil(Value * 1.1, 0.1),            Ceil(ThresholdRed * 1.1, 0.1)            ),        1        ),    1)

This way we will obtain the following result, where the thresholds and the value of the indicator will be shown much clearer:

Axis inversion

The biggest challenge, since Qlik Sense, although it does allow the segments to be dynamic, does not allow having dynamic colors for them, and in this case, they should be based on the fact that the Green threshold is greater or less than the Red threshold.

The idea to solve this problem is to use two Red segments and two Green segments on either side of the central Yellow segment. In this way, one of the two can be canceled on each side of the Yellow segment depending on whether the scale is direct (Green-Yellow-Red) or inverted (Red-Yellow-Green).

🤔

If we put all the segments considered in a horizontal version of the indicator, we would have something like this:

In the case of a direct indicator, we will have to make the red segments at the beginning and the green at the end disappear, making it appear as there are only 3 sections, green-yellow-red:

In the case of an inverse indicator, we will have to make the green segments at the beginning and the red at the end disappear, making it appear that there are only 3 segments, red-yellow-green:

Below are the behaviors that the resulting indicator would have, both with direct and inverse values:

Direct examples

Inverted examples

To achieve all these improvements, the proposal would be to modify the following properties of the visualization:

• Add-ons:
  • Green:
  • Color: #46c646
  • Expression:
• Reference lines:

=ThresholdGreen

• Red:
  • Color: #f93f17
  • Expression:

= ThresholdRed

• Appearance:
  • Footnote:
• General:

='Green: ' & Num(ThresholdGreen, '0.00%') & ' - Red: ' & Num(ThresholdRed, '0.00%')

• Presentation:
  • Min:
• Range limits:

=If(ThresholdGreen < ThresholdRed,0,    If(Value > 0.1,        If(Value < ThresholdRed,            Floor(Value * 0.9, 0.1),            Floor(ThresholdRed * 0.9, 0.1)            ),        1        ))

• Max:

=If(ThresholdGreen < ThresholdRed,

    If(Value < 0.9,

        If(Value > ThresholdRed,

            Ceil(Value * 1.1, 0.1),

            Ceil(ThresholdRed * 1.1, 0.1)

            ),

        1

        ),

    1

)

• Segments (limits):

• Green1:

=if(ThresholdGreen<ThresholdRed, ThresholdGreen, 0)

• Red1:

=if(ThresholdGreen<ThresholdRed, ThresholdGreen, ThresholdRed)

• Yellow:

=if(ThresholdGreen<ThresholdRed, ThresholdRed, ThresholdGreen)

• Red2:

=if(ThresholdGreen<ThresholdRed, 1, ThresholdGreen)

• Measure axis:
• Scale: Wide

Tip: When adding all the segments using the above expressions, it could be difficult to select the correct segment (specially the hidden ones) when you want to change the color. A trick is to first create the limits with fixed values at the beginning, and customize the colors, later we can edit the limit expressions:

Drawbacks

The only drawback I see to this solution is that Qlik Sense always tries to draw the segments, even if they have no width. So, lines could appear in the separations of each segment due to the segments with width = 0:

It is hardly noticeable, but it does appear sometimes.

Feel free to comment and provide any feedback or new suggestions to improve this kind of charts.

Juan Gerardo Cabeza

Account Manager at Mercanza

Both QV and QS can use the following methods to achieve "Cycle Group".

1. Create "Inline" as a filter in LOAD Script.
DimensionFilter:
LOAD '[' & [Dimension Selection] & ']' as [Dimension Selection] Inline [
Dimension Selection
Customer Channel name
Customer country name
Customer name
FacilitySold
Item desc
Origin Description
Responsible
Species
State Description
Warehouse
];

2. Create variables
!!! Attention!!! The variables here must be added "="

=GetFieldSelections([Dimension Selection])

3. Add variables to the dimensions of the chart

=$(vDashboardDim_A)

4. Create Multi Boxo as an option

Hope it helps you:)

Ok, here's another batch of tips when building visualizations in Qlik Sense. It's a mix of recipes for new charts and tips for app development. Content as follows, full descriptions and demos in the app below.

Charts

• Radial bar chart / Activity gauge
• Violin plot
• Circle timeline
• Fish Eye chart
• Stacked Venn chart
• Radial bar chart revisited
• Stacked butterfly chart
• KPI with org chart
• Kanban chart with org chart
• Kanban chart with table
• Gantt - map chart version
• Pie chart with center text
• Switch
• KPI with text &  image object

Tips

• Custom data labels
• Color by rank
• Legend sort order
• Text & image export
• Donut map layer
• Compare with last year
• Compare with last period
• Document chaining
• URL encoding
• Land or population
• Avoid formulas in selection bar
• Custom pivot totals
• Pick and match
• Line break in button
• Pivot with multi lines
• Autoscale
• Charts for animation

I want to emphasize that many of the tips are invented by others than me, I tried to credit the original author at all places when possible, the app below can be viewed as my current top picks.

If you liked it, here's more tips in the same style:

• Sense Visualization tips 2020 Qlik World
• Sense Visualization tips 2019 part 2 
• Sense Visualization tips 2019 Qonnections

Similar but for Qlik GeoAnalytics :

• Qlik GeoAnalytics tips 2020 QlikWorld
  
• Qlik GeoAnalytics tips 2019 Qonnections
• Qlik GeoAnalytics tips 2018 Qonnections

Thanks,
Patric

First of all, I'm very grateful and amazed by Qlik Sense and the Qlik community. I think I've learned so much more in the last couple of months than in the last couple of years.

Specific thanks to Fabrice Aunez from whom I took the idea for Time Analysis and just adapted it to my requirements.

Many thanks to Henric hic for all his posts and comments. Legend!

Ok, to the point, the intention here was to streamline the creation of a Master Calendar and time dimensions based on one or more than one (if required) important dates on your data model.

This subprocess creates several tables that should facilitate set analysis formulas for Calendar, Financial and even Seasonal analysis such as

• Current Calendar or Financial Period
• Previous Calendar or Financial Period
• Previous Calendar or Financial Year
• Current Calendar or Financial Year to Date
• Previous Calendar or Financial Year to Date
• Current Calendar or Financial Year Moving Total 3 Months
• Previous Calendar or Financial Year Moving Total 3 Months
• Current Season
• Previous Season
• Previous Year Season

And few auto calendar fields for the given date or dates compared against today's date:

• In Year to Date (Flag)
• In Financial Year to Date (Flag)
• In Month to Date (Flag)
• Years Ago (Number)
• Months Ago (Number)
• Month Relative Number (Number)

I created this routine mostly for Australian organisations but then adapted it to allow for other configurations, although not a great deal of testing has been done to be used in other countries, so it'll be up to you to check.

At the moment, the seasonal analysis is done only for Australia, where the Seasons are:

• Summer: Dec / Jan / Feb
• Autumn: Mar / Apr / May
• Winter: Jun / Jul / Aug
• Spring: Sep / Oct / Nov

I imagine that depending on the feedback, I might redo the code to allow for other configurations of Seasons.

I haven't had the chance to work with Qlik View yet, only with Qlik Sense and the main driver for our developments in to empower users to do ad-hoc analytics, which means we need to make it a bit easier for them and with this data model we were able to create all measures that require time analysis as Master Items

I started with Qlik Sense 4 months ago and I only know few functions, so when you look at the code and think 'hmmmm.... there's got to be a better way to do this...', you'll be right!!!! I just haven got there yet...

I hope you find this useful. Many thanks for any constructive feedback!

Kind regards,

Luis

At Qonnections 2019 I hosted a session with tips called “Qlik Sense Visualizations, best practice and top tricks”. I have received a lot of positive feedback, so I decided to make a part two with 41 more or less useful new tips.

Positive and negative bars in the same column
Bullet chart in table
Lollipop chart
IBCS charts
Special sum in table
Gauges
Mix value and percentage
Horizontal filters
Sharper tables
Lines and markers
Distribution plot tips
Annotations
Stacking and grouping
Vertical waterfall
Alternate states
Current selection
Show conditions
Select by list
Search options
Brushing
Pivot table sort
Pivot sort part 2, Pareto analysis
Add descriptions to charts
TreeView
Comparing tables
Best selling combo
KPI with indicators
Venn diagram
Venn diagram nr 2
Container tips
Master item management
Drill down bread crumbs
Target chart with CSS
Triggers and email alerts
Cyclic measures
Line graphs with bands
Color bands
Dimension dependent master measures
Control Charts
Check boxes and radio buttons
Filter sliders 

I want to emphasize that most of the tips are invented by others than me, I tried to credit the original author at all places when possible. Many of the tips have been published before on the Qlik Community, the app below can be viewed as my current top picks.

If you liked it, here's more tips in the same style:

• Sense Visualization tips 2020 part IV
• Sense Visualization tips 2020 Qlik World
• Sense Visualization tips 2019 Qonnections

Similar but for Qlik GeoAnalytics :

• Qlik GeoAnalytics tips 2020 QlikWorld
• Qlik GeoAnalytics tips 2019 Qonnections
• Qlik GeoAnalytics tips 2018 Qonnections

Thanks,
Patric

The Visual Vocabulary created by the Financial Times Visual Journalism Team is a great tool for driving data discussions and picking the right chart. Richard Speigal at NBS made an awesome implementation of it using Qlik Sense and the Picasso Designer. Here's link to the app:

https://community.qlik.com/t5/Qlik-Gallery/Nationwide-Building-Society-Visual-Vocabulary/bc-p/1761656

And here's the post describing the app and how to use it:

https://blog.qlik.com/designing-the-nbs-visual-vocabulary-how-an-idle-challenge-turned-into-an-everyday-bi-tool

Here's a variant of the same using without extensions, see app below for details.

Deviation

Distribution

Change over Time

Spatial

Drill-down groups allow you to form a single field dimension that create a natural hierarchy of existing fields in the table model. With drill-down groups you can first display a high-level summary and then "drill" into the detail from one or multiple Sense visualizations.

Learn how by watching this video:

NOTE: The Qlik Help Channel on YouTube has the latest and greatest how-to's for the latest releases. 
Don't forget about the Qlik Online Help as well. It has videos, samples, tutorials and more.

How we can  creating a gantt chart in qlik Sense without use any extension .

Ex: we can  also create  gantt chart in qlik Sense through anychart extension .

This doc and app shows a workaround how to make cyclic groups for dimensions and measures. Qlik Sense 2018 November or later is required. The workaround is based on a variable and the button. The solution is not self service but no extensions or add-ons are required and using master items simplifies the usage.

Cyclic Dimension Group

1. Add a variable vDimension and set it to Year.
2. Add a button, set the label to =vDimension and add an set variable action for vDimension to toggle the dimensions:
   =pick(match(vDimension,'Year','Quarter','Month'),'Quarter','Month','Year')

Write dimension dependent expressions using vDimension (instead of GetCurrentField), ex:
=Sum(Sales)/ Sum(Total Aggr(Sum(Sales), $(=vDimension)))

For convenience, add master dimension CyclicDimension =$(vDimension) set to label =vDimension and make the button a master visualization.

Cyclic Measure Group 

1. Add a variable vButton and set it to Sales
2. Add a button set the label to vButton and add a set variable action for vButton to toggle the measures:
   =pick(match(vButton,'Sales','Orders','COS'),'Orders','COS','Sales')
3. Add a variable vMeasure and set it to hold the measure expressions:
   =pick(match(vButton,'Sales','Orders','COS'),'sum(Sales)','count(OrderID)','sum(COS)')
   
   

For convenience, add master measure CyclicMeasure =$(vMeasure) set to label =vMeasure and make the button a master visualization.

This technique has been mentioned before in: 
Top-10-Viz-tricks-Qonnections-2019
Top-10-Viz-tricks-Part-2

There are several proposals for cyclic groups, thanks for the inspiration:

Juraj Misina, https://dataqlues.com/getobjectfield-function-and-alternate-dimensions/

Github Download QVF/QVS 

• Tasks in a Gantt Chart (requires Google timeline extension).
• Show Task intensity.
• QVDs with all their metadata information, including fields.
• Scripts analysis (via logs) and the dependencies of your scripts for the QVDs they use. Useful to debug and see the pain points in your scripts and how much time these portions take to load).

Gantt chart of TasksTask intensityQVD info - tables and fieldsScript analysis - load time and key words searchCross table of QVD tables and fields

You will need to run a script to loop through all the Governance Dashboard table in order to store them in QVDs.

This loop is available in the "05a*. CONFIG" tab.

The tabs 05a to 05f are to analyse log files and link them back to the tasks. It then will follows the logic and data model which goes from  Task > App ( > Script) > QVD.

Set the library connection to "vQMC_QVD_Store" where the Governance Dashboard QVDs are contained.

Set the URL to the QlikSense server you are analysing in "vQlikSenseServer".

In the "Full_Paths_To_Folder" inline table, set the library paths to the folders where you generate and store your QVDs from your scripts.

Please check the script portion just above this table, as you can set times during the day when you run the QVD files analyses. The QVD analysis portion of script can take long depending on the amount of QVDs you have. The rest of the task analysis portion should take less than a minute.

In order to run the script analysis test, set "vRunLogAnalysis" to 'Yes' and set the location of the folder where the log files are contained in "vScriptLogFolder".

If you use standardised sub-routines (Calls) you can set the wildmatch-string search in the "Script_KeyWords_Categorisation_List" table.

The 05g tab will run a dependency table that will link all your tasks and apps depdendency on QVDs as written in your script logs.

/*In the comments below, indicate what features you want the most*/

/*Not all of this is my work. Some were voiced by my colleagues and other folks who I've met and had an opportunity to work with.*/

All,

I am creating this thread to gauge what features that we require in QlikSense for future releases.

I'll keep updating this thread periodically so we can have one consolidated list.

List of features:

1. Exporting Master Library of Dimension / Measures - Including all Metadata like colors / date created/ date modified.

@ Qlik - This is very useful for version control as multiple apps in the Org share same KPIs and Dimensions, it becomes easy for developers to re-use and maintain.

        1.1 Organizing Master Item list in 'Folders'

@Qlik - Being able to categorize the Master item list is pretty useful.

1.2 Ability to re-use Master measures within expression editor. (10/01/2018)

2. More shortcuts for developers - Like CTRL + R, CTRL + ALT + V etc.

3. Slider, Input Box, Dropdown (for Single select)

@Qlik - These may seem trivial but they're very useful, esp. in financial reporting. My team had to let go of a project because these features aren't available on QlikCloud  platform.

4. Sizeable Expression editor

@Qlik - This is pretty handy when the developers want to look at how the expression evaluates to. Currently the expression editor dialog box takes the whole screen and there's no easy way for developers to check the working of expression.

5. Variable dialog box should have a search / export option. Export option should fetch the metadata like Date created / Date modified and Tags, Description.

@Qlik -  This is extremely useful when you have a large set of variables. My app has over 1200 variables and currently there's no easy way to search for a particular variable. Giving the ability to export the variable data would save lot of time for developers as it can be used for QCing the data.

          5.1 Variables itself should have checkboxes.

  @Qlik -Checkboxes allow multiple variables to be deleted at once.

6. Tags created by developer should be made SEARCHABLE by developer / admin

7. 'Extend sheet' option has a faint scroll bar. Harder to spot.

8. Bookmarks should have the option to be shared with other users.

9. Comparative Analysis should be made a standard feature. (10/01/2018 - Now available)

10. Trendlines should be available as a standard option.

@Qlik - Eg. Linear, Moving average, Polynomial, Exponential etc. Currently it involves a tedious process of using linest and aggr functions to achieve this.

11. Better tooltips - Including charts, KPIs etc.

12. Table headings should have formatting options like Bold / Italic / Coloring/ Wrapping etc.

            12.1  A 'where clause' in straight table.

@Qlik - 'Dimension limit' option exists in QS but if more than one dimension exists, all other dimensions should be limited for this feature to actually work. This becomes complex when multiple measures exist as well. The best solution is to have a global condition for a straight table. For e.g. if I have sales, profits, cost and 10 other dimensions, I could have a single condition to restrict rows.

Say, Sum(Sales) < 1000. This would only show the records for all possible combinations of Dimensions and Measures that have sales less than 1000.

An if clause could be used inside an expression to achieve this but it's clunky.

13. New Visualizations

               @Qlik -

1. Mini charts in Tables

2. Sankey chart

3. Trellis chart

4. Candlestick chart (For Financial trading)

5. Gantt chart (For operational flow)

6. Calendar selection object

7. Violin Plot

    https://blog.modeanalytics.com/violin-plot-examples/

8. Bullet Graph

9. Parallel Coordinates Plot (Pretty cool)

    https://datavizcatalogue.com/methods/parallel_coordinates.html

10. Coxcomb Chart

    Creating Coxcomb Charts in Tableau | Bora Beran

14. Folders in QlikSense Desktop

        14.1 Also, 'Folders'  - within - an app overview to group sheets together. Removes clutter.

15. Alerts in QlikSense (May require integration with QMC)

@Qlik - Say for example, the sales fall below 10,000$, there should be an alert sent to all the users via an e-mail.

16. Sheet level Access - Conditional show / hide of sheets.

@Qlik - This is super useful to show and hide sheets. Some of the requirements are such that certain visualizations should be 'hidden' for some users. This'll allow the developer to do that instead of going through a tedious process in the QMC.

17. Ability to search for fields in a TableViewer.

18. Scramble data in QlikSense.

@Qlik - While demoing apps to Sales people it's often best to obfuscate the data inside the app. QlikView has this feature which is super useful.

19. Folder Connection Pop-up should have a search option to lookup for specific files. Also helps if the Pop-up is larger

@Qlik -  It would be really useful to have a search option in the folder connections pop-up. This would allow folks to search for a specific file. Currently if a folder has a lot of files, the user is limited to scrolling wearily to the bottom to select a file. Also, if the Pop-up window were larger, it will be helpful to spot a specific file.

hic‌ mto‌ rwunderlich‌ ‌ jif‌ jgd‌ etk‌