When it comes to aggregating measures over time, our users frequently have the following requirements:

• To-Date ranges such as YTD and QTD
• Relative time periods such as "Previous Year", "Previous Month", or “same period last year”
• Rolling periods which are used for moving averages. (Ex. "Rolling 3 month average")
• Year-over-Year growth metrics

I call these “relative time periods” (or “RTP” for short).  Although there are similar requirements at the week and day level, RTPs at the month level are by far the most common.  This blog post discusses only the month-level RTPs.

As any Qlik developer can attest, fulfilling these requirements can sometimes prove challenging. Although set expressions are very powerful, they can often prove troublesome.  Coding the above date logic can quickly make your set expressions hard to read.  The syntax highlighting in the IDE is not ideal and the expressions are notoriously hard to troubleshoot.

I’ve often found that a data-driven solution is best.  Adding a time dimension is a huge step in the right direction.  Those of us that have a background in data architecture will be familiar with time dimensions.  For the un-initiated, time dimensions are often referred to as “Master Calendars” in the Qlik forums.  In your model, the time dimension joins to your fact table via a date field.  For example the join might occur on [SalesHeader.OrderDate].

The typical time dimension has one row per date.  The remaining columns are attributes of the date.  You might represent your date in various numeric or text formats.  You typically break the date components into separate columns: day, month, and year.  You might have long and abbreviated month names.  You might have a column for quarter.  Don’t get me started with weeks!

The time dimension is very denormalized: it has one primary key and a lot of repeating values in the attribute columns.  All these time-related attributes make the report developer’s life easier, providing ample columns on which to filter, concatenate, etc.

To represent relative time periods, you need to create several fields which denote a date’s inclusion in a named range.  For example, you might have a “CurrentYTD” flag which contains a ‘1’ for all the dates between January 1^st and today (the rest of the values are zero).  Now you can use a simple set modifier in your measure: Sum( {<CurrentYTD={1}>} Sales).  You can use this approach for other named ranges such as “Current Quarter”, “Rolling 3 months”, and “same period last year”.

This is a great approach, but now you need to generate your time dimension nightly; the flag values will shift as each day passes.  This isn’t a big deal.  We typically have apps that require nightly refresh.

So, you make a nice report that incorporates your new flags.  Everything looks great.  You have successfully delivered moving averages and year-over-year growth rates without your set expressions looking like a dog’s breakfast.  But then your user asks you for the same report “as at the end of last quarter.”  What?!!?

So what do you do now? Do you regenerate your time dimension based on an earlier day and reload your app?  Probably not.

Our requirement has just evolved: We now need RTPs, for EVERY month!

Fortunately, this can also be accomplished using a data-driven approach.  What’s more, we can leverage the time dimension we just created!

The solution is a mapping table which sits between the time dimension and the fact table.  It’s a many-to-many relationship that controls which dates are selected in fact based on two parameters:

• An anchor date
• A Relative Time Type (Ex. YTD, QTD, rolling 3 month, etc.)

So, now we can get any RTP by any anchor date!

Here’s a sample expression that yields YoY YTD Sales Growth as at April 2017.  I think it’s pretty readable.

(Sum( {<AnchorYearMonth={‘2017-04’}, RTP_Type={‘YTD’}>} Sales)

/ Sum( {<AnchorYearMonth={‘2017-04’}, RTP_Type={‘YTDPY’}>} Sales)) -1

Now, you can set your AnchorYearMonth to the current YearMonth or have it be selectable by the user. You could easily change the RTP_Type to current month or rolling 3 month to get similar year-over-year growth rates.

I’ve experimented with a number of approaches and this – by far – has been the easiest and most flexible to implement.  I’ve used it at several clients over the past 3 years.

I’ve created a reusable script library that makes it easy to create the mapping table based on your month dimension.  I encourage you to try out QlikRTP and let me know how it works for you.  It's available at GitHub.

QlikRTP

I credit Fabrice Aunez’s post on the Qlik Community forum for inspiration.

Simply create YTD, moving totals and comparisons versus Year Ago

Fabrice Aunez

Sep 17, 2013

Fabrice did a great job of describing his solution.  I internalized those concepts and endeavored to parameterize my scripts and make them easy for developers to incorporate into their models.

Note: QlikRTP currently works at the month level, although it can be adapted to work for relative weeks and days.

Goal:

The goal for this guide will be to use the rules capability in Qlik Sense Enterprise on Windows to segregate consumption of published Qlik apps (production activities) from ad-hoc development in the Hub. This guide will use both Load Balancing Rules in the first example. In the second example, we will use Security Rules in order "hide" the Streams when accessing the Development Virtual Proxy.

Example 1: Basic Segregation

For this example, we will review basic segregation of Qlik apps across multiple virtual proxies. In order to be successful with basic segregation the Engine(s) which will host the Qlik apps need to not include the Central Node. This is due to some un-modifiable rules in Qlik Sense Enterprise on Windows which will interfere with things [1].

To achieve basic segregation, the administrator will need to:

• Create a Custom Property named NodeType, which is applied to Nodes and has the possible values of Production and Development.

• Apply the Production value to the production only node(s) and ensure that the Node purpose for that node(s) is set to "Production" [2]

• Apply the Development value to the ad-hoc development only node(s) and ensure that the Node purpose for that node(s) is set to "Both" or "Development".

• Disable the default Load Balancing Rule ResourcesOnNonCentralNodes

• Create a new Load Balancing Rule.
  • Name: Separate Production from Development
  • Resource Filter: App_*
  • Actions: Load balancing
  • Conditions: ((node.@NodeType="Production" and !resource.stream.Empty()) or (node.@NodeType = "Development" and resource.stream.Empty()))
  • Context: Both in Hub and QMC

• Create a virtual proxy for the consumption of production apps. In this example it will have a prefix of prod, but this is not required. Use of the default virtual proxy is possible for this. Select the Server Node which is a production node.
  • Not shown: Configure any needed host white list values (Virtual Proxy > Edit > Advanced > Host white list)

• Attach to the desired Proxy (if needed)

• Create a virtual proxy for ad-hoc development. In this example it will have a prefix of dev, but this is not required. Use of the default virtual proxy is possible for this. Select the Server Node which is a development node.
  • Not shown: Configure any needed host white list values (Virtual Proxy > Edit > Advanced > Host white list)
• Attach to the desired Proxy 

• Open up the Hub on each virtual proxy to validate
• Dev:

• Production:

Limitations of Example 1

This approach does isolate the availability of apps between Qlik Engine nodes. Since there is no inherent relationship between the virtual proxy which the user used and the evaluation of the streams that the user has access to, then this approach will result in empty streams when accessing Qlik Sense Enterprise using the dev virtual proxy:

For most organizations this is an acceptable solution. For those who want to further customize the experience, if they are using a supported authentication mechanism (e.g. SAML), then they can implement Example 2 (see below).

Example 2: Advanced Segregation

Building off of the configuration done in Example 1 (above), we can further customize the experience by "hiding" the streams when the user accesses Qlik Sense Enterprise using the dev virtual proxy. This approach uses SAML for authentication due to the ability to pass static attributes on a per virtual proxy basis. Not covered but this approach can be adapted to any authentication type which allows the passing of session attributes. This includes JWT and Web Ticketing. Both of those mechanisms can have customized authentication modules which pass a session attribute which specifies a value which can be further used in the site's security rules.

To implement this approach:

• Edit the Dev virtual proxy and specify a static attribute to be passed like so:

• Customize all security rules which provide stream access to include the clause: and user.environment.access.Empty()[3] 
  • Note: In this example, I am have only a single custom security rule which grants access to my custom streams based on matching a user's group from a User Directory Connector to a custom property assigned to the stream. In your environment, you may have many rules which apply to streams which need adjustment.
  • Note 2: In this example I have likewise adjusted the default security rules StreamMonitoringAppsRead (which governs access to the Monitoring Apps stream) and StreamEveryone (which provides access to the Everyone stream)

• Open the dev Virtual Proxy to validate:

Extending Example 2

This guide shows how to segregate consumption from development using static attributes on a SAML virtual proxy. A similar approach could be done which applies different values for the Access static attribute on each virtual proxy. This would allow segregating both apps and streams:

• across business units 
• between internal and external access

[1]  These rules are the ResourcesOnCentralNode Load Balancing Rule and the OwnerRead Security Rule. ResourcesOnCentralNode ensures that all applications are load balanced to the Central node's Engine. This rule cannot be modified due to architectural assumptions in the design of Qlik Sense Enterprise. For example that apps which need to be migrated will be migrated by the Central node's Engine. The OwnerRead security rule ensures that the owner of some entity (e.g. app, stream, extension, sheet, etc) can see that entity. ResourcesOnCentralNode's consequence is that if the Central node's Engine is used for load balancing then all applications will be available to the user in the Hub. OwnerRead's consequence is that the owners of unpublished apps will be able to see those apps if the Central node's Engine is used for load balancing.

[2]  The Node Purpose value of Production disables the Create App button in the Hub over and above any security rules or license assignments (i.e. a Professional license) which would otherwise grant the ability to create an app.

[3]  Also ensure that all streams are owned by an internal account. As with note [1] above, if a user is the owner of a stream then they will see that stream despite the configuration in Example 2.

During a workshop, the most tedious part is to assign a QlikCloud user into the workshop space.  With Qlik Application Automation, this process can be automated from the creation of the space to the assignment of the user into the specific space.

First, I created a google sheet that contains the list of users email, and 1 sheet correspond to 1 workshop.

I used the List Sheets Of Spreadsheet block to list out all the sheets within this workbook

Then I used the Condition block to check whether the sheet is prefix with *.  This provide a mechanism to skip any sheet by renaming the sheet.

If it is not prefix with *, the Create Space block will create a space for each sheet using the name of the sheet.  You will need to set the type of share and most importantly, set on error to ignore and continue automation and ignore errors.  This will ignore this block when the space has already been created.

Once the space is created, the Search Spaces block is used to retrieve the ID of the space as the space assignment is by the ID

Next, the Get Data From Sheet With Headers block is used to retrieve the list of users email from the google sheet

For each user email, the Search Users block is used to retrieve the ID based on the email address.

With both the Space ID and User ID, the Add Member To Space block is used to assign the user into the space.  Note the type of roles to assign to the user and the type of member.  Also set the on error to ignore and continue automation and ignore errors in case the user has not signed up yet.  This will ignore this user and move to the next user.

The following is the entire flow of the Qlik Application Automation

This can then be scheduled to run everyday and possibly more frequently just before the start of the workshop.

When dealing with OAuth2, it is common to have a two-step process which involves you first requesting a refresh token, and then using that returned access token in the subsequent request. Qlik’s out of the box REST connector doesn’t allow for the input of variable parameters in its UI, so at first glance, it appears as if every time you load your application, you would have to first generate a new refresh token and manually input it into the second REST call. There is in fact a way to make this process dynamic however by levering the ‘WITH CONNECTION’ clause. We must alter the existing connection in the script to make it dynamic.

Monitoring current performance and availability of Qlik Sense site is not out-of-the-box feature, but can be easily achieved with native REST connector and requesting engine\healthcheck JSON API.

There are other tools and approaches, but since I already have the best BI tool available 😉, I found it reasonable to use it. All that is necessary is just:

1. download and import the attached app
2. create REST Data Connections and adjust the settings in the script
3. schedule app reload

This Qlik Sense app:

• loads engine\healthcheck JSON data from multiple nodes
• parses and consolidates it to two tables
• stores them to QVDs with all previous snapshots
• visualizes key metrics of engine utilization and availability in time

It's recommended to deploy and schedule this app on DEV or TEST site, pointing to all production nodes. Otherwise, there will be no data loaded in case the production engine (or the whole server) crashes.
(just FYI - DEV and TEST sites are included in the professional license subscription)

It's also recommended to read the official help site info about the engine API before you deploy this app.

After importing the app to a suitable site, follow these steps:

1. Decide on target folder for result QVD files, where parsed snapshot of JSON data will be stored. Create a folder Data Connection (if it doesn’t already exist) and set its Lib name to vQVDpath variable in Settings script section.

2. Create a REST Data Connection to each node:

URL: https://<server domain>/engine/healthcheck OR https://<node IP address>:4747/engine/healthcheck

Authentication Schema: Windows NTLM
User name & password: type root admin or service user credentials
check "Skip server certificate validation" if there is no valid certificate on target node

Query headers:
User-Agent: Windows
Cache-Control: no-cache

3. Adjust the INLINE table in the Settings section of the script according to your site

node - name of the node that will be shown for app’s user
restConnection - name of the REST connection that points to node’s engine/healthcheck
RAM - amount of node's memory in GB
CPU - number of node's logical processors

4. Schedule a reload task. The recommended app reload frequency is 5 minutes or less (tested reload duration on the 4-node site is 12 seconds). The script will return "N/A" in [isSaturated] field when the engine is not responding.

The current roadmap includes - script for email notifications, possibility to load only N days back, mapping app name to IDs, node comparisons.

PLEASE! If you make adjustments or additions to the script or front-end visualizations, please share them back on the Qlik Community.

Happy Qliking 🙂
-RADO-

I like mathematics and see the potential it is not only in robotics or “almost sci-fi” technologies but everywhere where data is generated and should be understood.

I like Qlik, its possibilities and simplicity for end-users at the same time.

However, when it comes to mathematics and Qlik together, a lot of people think about SSEs – python or R – and default Qlik math functions are underestimated. I think there might be two reasons – Qlik users do not know about them or do not understand when these functions can help them. So, I decided to develop a Qlik app that will get you an overview of default mathematical functions in Qlik with a little bit of theory and above all, examples and exercises. All of them are interactive – thanks to Qlik 😉.

In the QS Math & Statistics app v3.0 you can find:

• Quiz to test your skills with Qlikculator I am very proud of 😄 (what to do for 5 hours in a train without wifi)
• An interactive overview of basic math functions with examples
• Descriptive statistics – theory, example, and my thoughts:
  • Measures of central tendency
  • Measures of variability
  • Percentiles, quartiles, fractiles
  • Measures of distribution
  • Overview of different datasets
• Intro to correlation
• Financial functions
  • Overview with the Payment calculator
  • Future Value & Present Value

If you will find the app useful, in next versions I will add:

• Outliers & extreme values – why and how to identify them
• Financial aggregation functions (IRR, NPV, XIRR, XNPV)
• Linear regression with examples not only for prediction
• Combinatorics examples
• Statistical distribution functions

Thanks, @radoresky  for your help and review of the app 

I hope you will like it. Any recommendations are very welcome 😉,

Maria

When building mapping visualizations in Qlik Sense, you will need either Lat & Long data to plot points on a map, or shape data to plot areas. For the latter, Qlik Sense will recognize KML files as a data format and load those natively. But shapes are merely a series of lat & long points that when plotted will connect up into a shape, such as a state or a sales region. When multiple shapes are plotted they can connect to form a country, or even a map of the world.

When plotting either points on a map or a series of shapes, you will need either the relevant Lat and Long data or shape data in your data model. It must also be associated the relevant field in your data model to allow you to plot the relationships. For example, to plot a series of bubbles on a world map to indicate population, you will need lat & long data for each country and you will need it linked to your population data on say country code.

UPDATE (8/7/2017): The new mapping object in Qlik Sense allows you to render maps without actually having the shape or point data in your data model. This is because the map object leverages a geospatial engine that recognizes country, state and town names dynamically. For more information refer to our help: https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Visualizations/Map/Map.htm

But where can you get such a data set? Well, there are many free location based data sets on the web, but often they are a bit fragmented and incomplete. To save you all a bit of time I have joined together a few files into a single world based location set, which contains the following for each country:

- Latitude & Longitude

- Country Shape data

- 2 char ISO code

- 3 char ISO code

- Short Name

- Full ISO Name

These are contained in the attached QVD file which I hope you find useful. When I get a chance I will also upload a Qlik Sense app with a few examples using the data. If you need some info on how to use the mapping object check out this great video from Mike TaralloQlik Sense - Creating a Map Visualization

Hi Team,

This document can help about configuration for Task failure notification in Qlik Sense

Connector installation and configuration

This part only needs to be done once on all Qlik Sense nodes per environment.

Prerequisites: Anonymous authentication to the <Company> SMTP relay has been opened. 

1. Download the Qlik Web Connectors March 2020 Patch 1 (currently installed) from the Qlik download site.
Go to www.qlik.com to log on with a registered customer or partner account, select Services > Customer Downloads, and then select the Qlik Connectors tab.

2. Before you unzip the file, check that the folder is not blocked by Windows security. To unblock the zip file, right click on the file, select Properties, then click on Unblock in the General tab. Click Apply.

3. Unzip QlikWebConnectorsX.X.zip (where X. X - is the version name) and place the unzipped files in a folder on the root directory of one of the nodes Windows server D:QlikWebConnector

4. Start up the Web connectors server in manual mode to configure it: open 😧 QlikWebConnectors\QlikWebConnectors.exe
If successful the following window will open.

5. Proceed to http://localhost:5555/web to configure the SMTP connector. Qlik SMTP connector is available on Connectors / Standard page. Click on the connector name to go to the connector Parameters page.

Fill in the details:

SMTP Server: Talk to windows team 
Port: 25
SSL Mode: None
To: [Your Email Address to test]
Subject: Qlik Sense Failure Notification
Message: Qlik Sense Says Hi !!
HTML ON
From Email: Your From Address 

Send a test email by clicking on Save Inputs & Run Table. You can see the result in Data Preview section and must receive the test email

6. Close the Web Connectors Page and the Qlik Web Connectors window to stop the connectors server.

7. Edit "Install Service. bat" file in D:QlikWebConnectors, Batch Files to configure the connectors as a service, so they auto start up with the Windows as follows:

REM *****************************
REM IMPORTANT - PLEASE READ:
REM * You should ensure than you have 'unblocked' the Qlik Web Connectors download zip file BEFORE unzipping it and running this.
REM * You need to change the following paths to match your .NET installation directory and QlikWebConnectorsSVC.exe location.
REM * You should run this file as Administrator (right click, 'Run as administrator').
REM *****************************
"%windir%\Microsoft.NET\Framework\v4.0.30319\installutil" "D:\QlikWebConnectors\QlikWebConnectorsSVC.exe"

pause

8. Save the batch file… Right click the Install Service bat file and select Run as administrator.

      a. The batch file installs the Qlik Web Connectors as a Windows Service.

when the batch file finishes, press any key to close the command window

you should see an entry for Qlik Web connectors like

9. From the Windows start menu, type Services to open the Services dialog.
      a Right click on the Qlik Web Connectors service entry
      b. In the Qlik Web Connectors Properties dialog, click on the Start under the General tab to start the service.
          Ensure that the Service status is Running.
          Warning: Close the Click Web Connectors before you complete this step. If they are running when you press start, you will get a message saying that the service has started and then stopped.
      c. Select the Recovery tab and set First failure to Restart the Service and set the time for Restart service after to zero (0) minutes. 

Apply the changes and click OK.

10. The Qlik Web Connectors should be available at http: //localhost:5555/
11. Install and configure the connectors on all other Qlik Sense nodes. The easiest way to do it:
      a. Stop the service and copy the folder D:QlikWebConnectors, to all other Qlik Sense nodes
      b. Register the service on each of the nodes (steps 8-10).
      c. Start the service which has been stopped at step (a).

12. Create new connection in every Qlik Sense environment not the nodes.
      i. Data load editor - Create new connection - REST
      ii. URL: Can be found from 5th point
     iii. Authentication Schema: Anonymous.
     iv. Skip server certificate validation: ON
     v. Query parameters:
          1. to: your emal for test
          2. subject: test
          3. message: test
     vi. Name: SendEmail
     vii. By default the connection name created via the Data load edit will be something like "SendEmail (<usemame>)". Remove the user name from the connection name via QMC.

Till above the connection setup is done.

How to setup the email alert for existing dashboard and newly creating dashboard?

1) Let's say, I have an Qlik Sense application called QSAppName.qvf {QSAppName is Dashboard Name} and this dashboard created task either daily/weekly/monthly etc.

2) To create alert, Again one application required

      a) Let's say, I've created an application called QSAppName_Failure_Notification.qvf 

      b) Go to Script / data load editor, And add the following script

3) Create task for "QSAppName_Failure_Notification.qvf" on dependency of failure to "QSAppName.qvf"

Bingo, You achieved very powerful feature enjoy with your email's. I love to hear some question's, If any using Comment box 

Regards,

Anil Samineni

I have always wondered how to change the theme in application with native qlik sense extensions.
I am aware that there are extensions on the market for the same purpose but the challenge was how to do it with the objects in the application itself?

My solution is to chenge background layer but this can be apply on theme too, you just need to add your css for theme in multy kpi object :

1. Use Multy KPI extension and add some css. I add 9 multy kpi object with different css for background and add to master items.

2. Create variable vBackgroundLayer and for definition use number 1 or any other number what will be default background layer in app.

3. Add Container and insert all 9 multy kpi objects and add condition
=if(vBackgroundLayer=1,1,0)
=if(vBackgroundLayer=2,1,0)
=if(vBackgroundLayer=3,1,0)
.............................
.............................
=if(vBackgroundLayer=9,1,0)

4. Using 9 buttons we can change the background lyers by color and position
Button settings:
Action: Set Variable value
Variable: vBackgroundLayer
Value: 1

Action: Set Variable value
Variable: vBackgroundLayer
Value: 2

Action: Set Variable value
Variable: vBackgroundLayer
Value: 3

.........................
.........................

Action: Set Variable value
Variable: vBackgroundLayer
Value: 9

5. I don't need any more container to be visible for that purpose i hide the container adding css in my multy KPIs objects

[tid="vkbDYYG"].qs-container
{
display:none!important;

}

In our environment we linked both QlikView documents and Nprinting reports within the unified hub interface of Qlik Sense. However, these links did not get removed at all, which was making the interface not very user-friendly.

If an actual report is removed from NP server, the report stays on the server. This is reported as a bug (OP-6802). The below text contains a work-around.

There is a major difference between the Nprinting reports within Unified hub and QlikView documents.

• With Nprinting reports - Qlik Sense Proxy acts as a - proxy. The URL would indicate that the report is coming from the Qlik Sense Server. The URL here for a report would be e.g. https://QLIKSENSESERVER/sharedcontent/e86dfdfd-a0d4-4cc4-9053-ce9ce1a53c8a/20180110T090524Z/report01.xlsx
• With QlikView document links - Qlik Sense just adds a link to the direct report. The URL to which the link took you would be e.g. http://QLIKVIEWSERVER/QvAJAXZfc/opendoc.htm?document=FOLDER%2FDOCUMENT.qvw&host=QVS%40SERVERNAME .

You can read more about unified hub on the help site: Qlik NPrinting reports in Qlik Sense ‒ Qlik Sense .

Deleting the links for the Nprinting reports

Attached is a PowerShell script which checks if there is a valid task for the report, which in case it stays. ( cleaning_Nprinting_reports_on_hub.ps1 ). Your account running this service must have the rights to see all the shared content on the server. A solution could be to add the following security rule (for shared content):

Resource-filter: SharedContent_*

Actions: Create, Read

Conditions: ((user.userId like "*" and resource.owner.userId=user.userId)) or ((user.userId="SERVICEACCOUNT"))

Context: hub and QMC

The first part of the script ensures that only users can see what is posted to them, and noone else. The second part ensures that the service account (running the attached PowerShell script), can see all objects.

The script is first using QS APIs to verify which reports exists on the hub, to find the metadata-tag for which task-id it was generated with. The script then uses this task-id for the report and cross-checks using NPrinting APIs to check if the tasks actually exists, where upon it either does nothing or removes the report on the QS hub.

If there is no task, the report is removed.

If there is a task but the user is just removed from the task, the old report will stay, you will then need to either clean manually or clean all. Most of the times - it doesn't matter as the user is looking at old information which he/she once had access to.

"Created date" within the Qlik Sense hub section "Reports", means the date when the first link was established.

Any report update timestamps will not be shown in the "created date", meaning it is a good practice to keep date/time within the report. There are no dynamic report names within the hub, or Newsstand, only email and such.

The script can be scheduled with Windows Task Scheduler, e.g. once a day to clean up links. Usually it only finds links on the very first run, or if something is changed.

It is possible to just clean all the Report links with another script ( just_clean_everything_on_hub-reports.ps1 ). That script is only based on QRS API. It is not recommended to just look in the folder of C:\<QlikRoot>\StaticContent\SharedContent and use the ID there to process the DELETE API command, as some reports is still available on the hub. The ID there though, e.g. C:\<QlikRoot>\StaticContent\SharedContent\e86dfdfd-a0d4-4cc4-9053-ce9ce1a53c8a corresponds to the id in the URL https://QLIKSENSESERVER/sharedcontent/e86dfdfd-a0d4-4cc4-9053-ce9ce1a53c8a/20180110T090524Z/report01.xlsx .

Deleting the links for the QlikView Documents.

There is also a cleaning script for QlikView Doc links to remove all links ( just_remove_everything_on_hub-qlikdoclinks.ps1 ). Naturally this can be modified to only use a specific folder or similar by using the WHERE command within the pipe of the PowerShell script. The service account requires access to all links (see above for security rule).

Currently it is not possible to unpublish from the Qlik Sense Hub. As a work-around, you can modify the distribution task to distribute only to a single administrative user which will remove the links to the QlikView documents from other user’s view.

The end-user can within the Qlik Sense hub section of "QlikView Documents", also right click > Delete.

Any changes to a QlikView task will create a second link, setting up a Unified hub link should thus be the last thing you do when scheduling.

Good luck!

Changelog:

• Version 1.0 - 2018-01-12 (Initial version)
• The scripts are tested with Qlik Sense September 2017 and Nprinting September 2017

[Paragraph format. Descriptive text here. See Content Guidelines for details. You can remove sections that are not needed for your article. If subheaders are needed, use heading 3 or heading 4 formats.]

Description:

• By default Straight tables only allows maximum of 3 lines in a cell to be visible and Pivot tables are limited to one line only

Resolution

By using a Multi-KPI object we can inject custom CSS onto the sheet to override the default limitations on pivot & straight tables

Step1:

Drag a Multi-KPI object onto the sheet  and add a dummy measure example = 1;

Next, on the Properties panel of the Multi-Kpi object navigate to Appearance >> Styles

Add CSS here

Step2:

Paste the custom below

For Straight Tables add the below CSS snippet in the Styles(CSS) box

.qv-st .qv-st-value .qv-st-value-overflow {
max-height: none;
}

For Pivot Tables add the below CSS snippet in the Styles(CSS) box

.qv-pt .cell .value {
max-height: none;
white-space:pre
}

As shown below now each cell will fit to content of the cell based on line breaks in the content

Output

Cheers

Vineeth P

Welcome to the QS CSS MasterClass.

Motivation:

In my career as a PreSales, I need to create quite a few "user-appealing-applications". Sometimes users request a specific design or specific functions I need to implement in Qlik Sense. As we all know, Qlik Sense is built for simplicity & self-service and sometimes it could be challenging to achieve the desired result. But through the last couple of years, there were a lot of tips and tricks around using CSS to create completely new designs and functions to implement a better information design concept.

This is where the MasterClass starts. I have created an application that gathers a couple of these tricks and explains them more in detail. In addition to that, it is very easy to understand because you can see the result directly within a Qlik Sense App. If you think this sounds interesting, take a couple of minutes and join my short tour through the app. I won't cover all aspects in detail. This tour will give you an overview of the documented and used tricks within the app.

Content:

The following list will give you a brief overview of what topics are focused on the specific sheet within the app.

Sheet	Description
Types of CSS implementation	You can add CSS definitions to your app by using themes or by using so-called "helper- objects". On this sheet, I will explain when you should use which option.
Hide (Helper-) Objects	A "helper-object" carries and injects the CSS definition on a specific sheet. This sheet helps you to hide this object and which objects can be used for it.
Colored Area Background	This example demonstrates how to add better guidance to your dashboards by segmenting your background.
Filter Pane Highlighting	Sometimes selections can be mandatory to consume a dashboard, or the creator likes to guide the user through the filter pane by using colored filter boxes. I show you how.
Hiding Button/Options	How to hide objects like the selection bar or elements within context-menus in case these functions shouldn't be used in the app or on this sheet?
Using Background Pictures	Adding background-pictures to your dashboard can spice up the overall flavor of your dashboards. This can be used for segmentation or just to add some style.
Table modifications	In this section, we will completely change the look and feel of a straight- and pivot table.
Moving Objects	Let's create an illusion by just moving our objects closer together. After that, I'll look like we just have one.
Changing standard fonts	This sheet explains the easiest way to implement your own font by using a custom theme. It's just a few lines of code.
Custom Grid Layout	No matching grid? This trick shows you how to create your own grid for a specific sheet by changing the metadata through Qlik Engine API Explorer.

Installation:

Under "Attachments" you can find the required package. The zip package includes a qvf file (Qlik Sense - CSS MasterClass V 1.0.qvf) and an extension (ShowHTMLfromMeasure). After importing the extension and app we need to change a quick configuration because the app has a different ID on your system now.

Open sheet called "Using Background Pictures". Normally this sheet has a background image. This gets referenced over the internal app-ID. Click on "Edit Sheet" and select the displayed CSS box ("CSS config HERE"). Navigate to submenu "Styles" and change the used app-ID in "Styles (CSS)" to your app-ID (displayed in the URL). Now you should see a background picture on this sheet.

Usage:

Every sheet has the same structure. First, a description explains the trick in general and how it's working. On most of the sheets, you also get the explanation for the used CSS selectors. On the right-hand side, you can see the used code in a black code box. You can't copy the code from here. To do so enter the "edit-mode" and click on the "CSS config HERE" button. This object is always the "Helper-object" that carries the used and explained CSS code. Navigate to the "Styles" submenu and copy the code from the "Styles (CSS)" window. I recommend using an external editor to modify or review the CSS code.

In case you like to transfer the trick to your dashboard you just need to change the object ID. If you don't know how to find the object ID this will be explained on sheet "Hide (Helper-) Objects".

This is a short summary of how I connect to my corporate Active Directory and extract employee records and group memberships for theses employees. This is the most time efficient way I have found that works in my environment, but there might other query options.

NOTE: The below example connects to domain.local. Please change these references to your domain name.

First step is to define a connection in Qlik Sense. I have found that OLEDB and ADsDSOObject provider works. For server side app deployment, make sure the service account is allowed to connect to the AD, or add name and password or a authorized user in the connection settings.

OLEDB CONNECT TO [Provider=ADsDSOObject;Integrated Security=SSPI;Data Source=ldap://domain.local;]

In the load script connect to the directory with a LIB CONNECT string.

LIB CONNECT TO 'LDAP Domain.local (tko)';

The below load statement extract all person records from AD. In the SQL query define the field names exactly as they are named in your AD. Notice, the FROM definition requires the LDAP URL to your domain.

ActiveDirectory:

LOAD *

WHERE Not(userAccountControl bitand 2); // Exclude inactive accounts

SQL SELECT

co,

department,

division,

employeeType,

givenName,

l,

mail,

manager,

mobile,

pager,

sn,

sAMAccountName,

telephoneNumber,

title,

userAccountControl,

displayName,

streetAddress,

postalcode,

st,

physicalDeliveryOfficeName

FROM 'LDAP://domain.local'

WHERE objectCategory = 'person' AND memberOf = '*'; // The * wildcard returns all records in Persons's objectCategory

Next step is to extract a list of all the existing groups in AD. Notice that your domain name has to be referred both in the FROM and WHERE in the SQL query.

ActiveDirectoryGroups:

LOAD

name AS GroupName,

name ,

distinguishedName;

SQL SELECT 

name,

distinguishedName

FROM 'LDAP://domain.local'

WHERE objectCategory = 'CN=Group,CN=Schema,CN=Configuration,DC=domain,DC=local';

Extracting all members of each group enables pairing groups with their exact members. Each group is queried to extract the member list.

FOR i = 0 to NoOfRows('ActiveDirectoryGroups')-1

     // Pick next group name and distinguished name

     LET vGroupName = Peek('name' , $(i), 'ActiveDirectoryGroups');

     LET vDistinguishedName = Peek('distinguishedName' , $(i), 'ActiveDirectoryGroups');

     TRACE $(i) of $(#vNoOfRowsActiveDirectoryGroups) : $(vGroupName);

     // Load person identifier from the current distinguished group

     // The result is stored in Temporary table

     // Each iteration is added to the same Temporary table

     TmpSamAccountNameGroups:

     LOAD

     sAMAccountName,

     '$(vGroupName)' AS GroupName

     ;

     SQL SELECT

          sAMAccountName

     FROM 'LDAP://domain.local'

     WHERE objectCategory = 'person' AND memberOf = '$(vDistinguishedName)';

NEXT

The extracted members are left joined to employee table, to map employees with their group membership. With the result of one table showing all employees and their AD group membership

// Left join to exclude sAMAccounts from group view that are not active

Left Join ('ActiveDirectory')

LOAD *

Resident TmpSamAccountNameGroups;

// Drop temporary tables

DROP Tables TmpSamAccountNameGroups, ActiveDirectoryGroups;

***UPDATED 06/30/2021

To resolve rest connector related issues visit:

• https://community.qlik.com/t5/Knowledge-Base/Qlik-Sense-June-2020-connecting-to-NPrinting-getting-quot-HTTP/ta-p/1759014

**UPDATED 10/16/2020

Revised Sense Sample to include updated cookie parsing and proper error handling

Added sample to trigger an NPrinting User Import Task from Qlik Sense

****

One of the most common questions is how can i distribute my NPrinting reports to occur automatically as soon as my Qlik app is done reloading ?

THis is possible in both Qlik Sense and with QlikView using the NPrinting API. Moreover, you can invoke the API through the the Qlik load script Qlik REST Connector v1.3+.  What this means is that you can schedule and/or chain your NPrinting report distributions using the Qlik Sense scheduler or the QlikView publisher to better integrate the operations across the platform

.The sample included herein draws on the great document published by gianluca perrin on Qlik Community here:   https://community.qlik.com/thread/292037

Benefits