Recent Documents
-
Distribute NPrinting reports after reloading a Qlik App
***UPDATED 06/30/2021To resolve rest connector related issues visit:https://community.qlik.com/t5/Knowledge-Base/Qlik-Sense-June-2020-connecting-to-NP... Show More***UPDATED 06/30/2021
To resolve rest connector related issues visit:
**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
- Common Scheduler
Currently NPrinting and QLik (Sense or View) have separate schedulers.
If you reload a Qlik application daily at 1:00AM and want to send the NPrinting reports directly after a successful reload, you must schedule them separately for times that you believe will not intefere with each other. Its better to have a100% chained solution however whereby reports are only distribute upon successful reload of the Qlik app. - Error handling
Should the NPrinting task, publisher has a built in solution to notify administrators. Qlik Sense also has 3rd party solutions to do the same.
Restrictions
You need to have v1.3 of the Qlik REST Connector. Its available as of Feb 2018.
Sample
Connection setup
You will need to create two REST Connector connections to the NPrinting API. One that uses a default HTTP method of "GET" , the other will use "POST" as the default method.Please reference pages 8-9 of the document https://community.qlik.com/thread/292037 to create the two connections.
App Setup
1. Import the attached application and open the script in the Data Load Editor
2. Modify the SETUP tab to reflect your
i. on lines 25/26 update the names of the two REST Connections that you create using the steps in 'Connection Setup' above.
ii. on line 30 , if NPrinting is configured to use https (it is by default) leave this setting to 'Yes' . If you reconfigured it to use http , set to 'No'
iii. On line 31 , set the NPrinting server and endpoint (host + port)
iv: Line 35 is the name of the NPrinting Publish Task that you want to execute
v: Line 37 and 38 is an enhancement to have Qlik check the NPrinting task executions for errors, or , if it runs too long, to abort it and report a failure .
3. (optional) You can also Import the following application into Qlik Sense to send an administrative alert. THis is not necessary with QV Publisher as it has an in-built feature to notify administrators when a publish task fails.
4. Open the imported app in the data load editor and update the 'setup' section
i. on lines 21/22 update the names of the two REST Connections that you create using the steps in 'Connection Setup' above.
ii. on line 26 , if NPrinting is configured to use https (it is by default) leave this setting to 'Yes' . If you reconfigured it to use http , set to 'No'
iii. On line 37 , set the NPrinting server and endpoint (host + port)
iv: Line 32 is the name of the NPrinting Publish Task that will alert an administrator about a report failure
(note that i've toyed with using the Notification Web Connector to send an administrative email alert in the event of a full blown NPrinting outage. This sample is a little simpler)
Task setup
In Qlik Sense QMC under tasks i've setup 3 tasks .
Task #1 - reloads a Qlik App
Task #2 - Distributes reports in an NPrinting Publish task (this is chained to execute 'on success' of Task #1)
Task #3 - Send an Administrator alert (this is chained to execute 'on failure' of Task #2)
QLIKVIEW
The QLikVIew solution has all the same benefits as the Qlik Sense solution with a couple more benefits.
Additional benefits:
- you can pass the vTask name from the publisher task to make for greater re-usability of the load script for chaining multiple tasks
- Publisher has a built in admin notification feature. SHould the NP task fail, the publisher task will also fail and an admin notification will be sent (you can do that with qlik sense but need to use a 3rd party qlik sense task notification solution, or use the load script to call an administrative task to send a notification. The latter can be done with NPrinting or with the Qlik Web Notification Connector.
Load script
The QlikView load script has a login , get task ID (lookup using task name), post task execution, and a task execution check tab. The task execution check tab will monitor the NP task execution on an interval with a configurable timeout. Should the NP task throw a warning or error, it errors out the publisher task. Should the NP task exceed the timeout, it kills the NP task and errors out the publisher task.You will need to update the NPServer, Task and timeout settings in the CONFIG tab. Again use the REST Connector 1.3 and i suggest testing with QV desktop first . The REST Connector must be manually installed on QV desktop and on the publisher machine to work.
New cookie parsing:
//Set Cookie header
let vCookieRaw = peek( 'Set-Cookie',0,'_response_header');
if vSSL='False' then
let vCookie= subfield(subfield('$(vCookieRaw)','Path=/,',3),';',1);
else
// let vCookie = TextBetween('$(vCookieRaw)','Secure,','Path=/',2);
Let vCookie = TextBetween('$(vCookieRaw)','SameSite=None,','; Path=/',3);
end if - Common Scheduler
-
Qlik Sense + DeepSeek and The Iron Throne
DeepSeek represents a milestone in the evolution of LLM tools. It exemplifies how innovative approaches can increase the accuracy and applicabilit... Show More -
Qlik & Python – Made for Each Other
Qlik Embedded Analytics offers a powerful solution to embed analytics capabilities directly into custom systems and applications, providing a more... Show More -
How to set up an Oracle OLE DB connection in Qlik Sense, step by step
This post explains how to connect to an Oracle database using the OLE DB connector and a tnsnames.ora file.Download the latest version of the Oracle D... Show MoreThis post explains how to connect to an Oracle database using the OLE DB connector and a tnsnames.ora file.
- Download the latest version of the Oracle Database Client for Windows (x64). You can use this link or Google it.
- Unzip the file and double-click on setup.exe.
- Select custom installation and follow the installation instructions.
- Choose an intallation folder (E.g., C:\oracle).
- Select components Oracle Net, Oracle ODBC Driver and OLE DB.
- Click on Next and finish the installation.
- Type environment variables in Windows' search bar and open the app. Then, click on Environment variables.
- Create a user variable named TNS_ADMIN and set its value to the path of the folder where your tnsnames.ora file is located.
- Open Qlik Sense and create a new OLE DB connection. In the Data Source textbox, type the name of the connection as it is in the tnsnames.ora file.
-
Setting up a WMS as a background map in Qlik GeoAnalytics
Sense Map chartExample adding a WMS background1. Add in Content Security Policy (Qlik SaaS)Origin: nowcoast.noaa.gov Directive : connect-src and img-s... Show MoreSense Map chart
Example adding a WMS background
1. Add in Content Security Policy (Qlik SaaS)
Origin: nowcoast.noaa.gov Directive : connect-src and img-src
2. Add Map chart
3. Add background layer WMS
url: https://nowcoast.noaa.gov/arcgis/services/nowcoast/radar_meteo_imagery_nexrad_time/MapServer/WMSServer
version:1.3.0
crs: EPSG:3857, transparent, png, layer: Image
Note, use CRS 3857 if the base map has the Mercator projection (default).
Pick CRS 4326 if the base map has the Adaptive projection.Link to documentation and example.
(QGA Extension map, old kept for reference)
Start by figure out the correct parameters. In this case the source is a WMS with data from the Dutch city Zaanstad.
Note, the urls below has chnaged and not working anymore, principles still applies)
- Easiest way to pick up the WMS source and at the same time verify that it works is through a demo page,
for example http://geo.zaanstad.nl/mapproxy/demo/?srs=EPSG%3A28992&format=image%2Fpng&wms_layer=Zaanstad - Select CRS (coordinate system), 4326 for use together with the default adaptive basemap or 3857 with the defualt basemap. Right click on the map image and "Open image in new tab..." . Copy the image url:
http://geo.zaanstad.nl/mapproxy/service?LAYERS=Zaanstad&FORMAT=image%2Fpng&SRS=EPSG%3A28992&EXCEPTIONS=application%2Fvnd.ogc.se_inimage&TRANSPARENT=TRUE&SERVICE=WMS&VERSION=1.1.1&REQUEST=GetMap&STYLES=&BBOX=77018.328125,474244.265625,153485.671875,512224.734375&WIDTH=1208&HEIGHT=600 - Remove the following parameters (these are filled in by the map) : BBOX, WIDTH, HEIGHT, VERSION, SRS.
That produces the url to use: http://geo.zaanstad.nl/mapproxy/service?LAYERS=Zaanstad&FORMAT=image%2Fpng&EXCEPTIONS=application%2Fvnd.ogc.se_inimage&TRANSPARENT=TRUE&SERVICE=WMS&REQUEST=GetMap&STYLES= - Fill in CRS and VERSION (in my example EPSG:3857 and 1.1.1 check the WMS url for the version).
Pro tip, "Load via Server" is practical to use if the the WMS doesn't allow CORS.
Pro tip 2, run Sense in the browser (http://localhost:4848/hub/my/work) and use the dev console (F12), good to spot errors from the WMS server.
- Easiest way to pick up the WMS source and at the same time verify that it works is through a demo page,
-
Advanced Scheduling in Qlik Sense Enterprise SaaS
Objective:For this document, we will review how to leverage Qlik Sense Enterprise SaaS' open APIs to build advanced reload schedules.Requirements:qlik... Show More{ "appId": "58f563b6-ccda-45b0-926c-e2992ca8b470", "recurrence": [ "RRULE:FREQ=MINUTELY;INTERVAL=15" ], "startDateTime": "2022-03-23T00:00:00", "timeZone": "America/New_York", "type": "scheduled_reload" }
This guide was written for use with the Windows PowerShell program. While PowerShell can be run on non Windows systems, use of bash techniques for parsing json (e.g. jq) is possible but not covered in this guide.
Examples of advanced schedules:
Reload a specific app every 15 minutes
# Specify the appId $appId = '<myAppId>' # Define the rrule $schema = 'RRULE:FREQ=MINUTELY;INTERVAL=15' $startDate = ((Get-Date)).ToString("yyyy-MM-ddT00:00:00") # Get the current date and convert to required format # Get the app's reload tasks $reloadTask = qlik reload-task ls --appId $($appId) | ConvertFrom-Json # Delete the associated reload task, if present if($reloadTask.data.Length -gt 0) { $null = qlik reload-task rm $($reloadTask.data.id) } # Create the task $null = qlik reload-task create --appId $appId --recurrence $schema --startDateTime $startDate --timeZone "America/New_York" --type "scheduled_reload"
Reload a specific app every hour on Thursday
# Specify the appId $appId = '<myAppId>' # Define the rrule $schema = 'RRULE:FREQ=HOURLY;INTERVAL=1;BYDAY=TH' $startDate = ((Get-Date)).ToString("yyyy-MM-ddT00:00:00") # Get the current date and convert to required format # Get the app's reload tasks $reloadTask = qlik reload-task ls --appId $($appId) | ConvertFrom-Json # Delete the associated reload task, if present if($reloadTask.data.Length -gt 0) { $null = qlik reload-task rm $($reloadTask.data.id) } # Create the task $null = qlik reload-task create --appId $appId --recurrence $schema --startDateTime $startDate --timeZone "America/New_York" --type "scheduled_reload"
Reload a specific app on the 2nd Monday of the month
# Specify the appId $appId = '<myAppId>' # Define the rrule $schema = 'RRULE:FREQ=MONTHLY;WKST=MO;BYDAY=MO;BYSETPOS=2' $startDate = ((Get-Date)).ToString("yyyy-MM-ddT00:00:00") # Get the current date and convert to required format # Get the app's reload tasks $reloadTask = qlik reload-task ls --appId $($appId) | ConvertFrom-Json # Delete the associated reload task, if present if($reloadTask.data.Length -gt 0) { $null = qlik reload-task rm $($reloadTask.data.id) } # Create the task $null = qlik reload-task create --appId $appId --recurrence $schema --startDateTime $startDate --timeZone "America/New_York" --type "scheduled_reload"
- Note: The hour which the reload is executed with daily / monthly / yearly is determined by the startDateTime value. In this example, the value is set to midnight, adjustment to align with desired business or batch hour is encouraged.
-
Backing up your Qlik Apps into Stream Folders using Qlik-Cli
Today, I'd like to share with you our current App backup solution using Powershell and Qlik-Cli.As you know, a production environment needs backups an... Show MoreToday, I'd like to share with you our current App backup solution using Powershell and Qlik-Cli.
As you know, a production environment needs backups and Qlik Sense Enterprise does not offer such a thing by default so I decided to build a solution.
Using Qlik-Cli for Windows, I put in place a solution that will not only backup my published and unpublished apps but will also create a folder structure to match your streams.
The unpublished apps are backed up inside the "My Work" folder using the app owner's name as a directory name. This allows to easily find the app owner when performing a search in the folders.
Square brackets in the App's name will be removed because you cannot create a file with these characters in Windows.
The script is scheduled using the Windows Task Scheduler with the following command:
Here's the code:
$ErrorActionPreference = "Stop" Try { # set export drive and directory D: cd D:\QlikBackups # connect to the Qlik Server. Replace <ServerName> with your Qlik Sense hostname Connect-Qlik -ComputerName <ServerName> -UseDefaultCredentials -TrustAllCerts # Export each apps from the previously defined streams to qvf Get-QlikStream | % {New-Item -Name ".\$($_.name)" -Force -ItemType 'Directory'} | foreach {Get-QlikApp -filter "stream.name eq '$($_.name)'"} | foreach {Export-QlikApp -skipdata -id $_.id -filename "$($_.stream.name)\$($_.name -replace '[[\]]','').qvf"} # set the export folder to the My Work area for unpublished apps cd "D:\QlikBackups\My Work" # Create a folder structure for all the app owners Get-QlikApp -full -filter "published eq False" | % {New-Item -Name ".\$($_.owner.userId)" -Force -ItemType 'Directory'} # Export each app into their folder Get-QlikApp -full -filter "published eq False" | foreach {Export-QlikApp -skipdata -id $_.id -filename "$($_.owner.userId)\$($_.name -replace '[[\]]','').qvf"} } Catch # In case of error, send an email to the Qlik administrators { $ErrorMessage = $_.Exception.Message $FailedItem = $_.Exception.ItemName $Time=Get-Date "Error: $FailedItem Message: $ErrorMessage Time: $Time" | out-file d:\log\QlikBackup.log -append $FailMailParams = @{ To = 'qlikadmin@MyCompany.com' From = 'MyQlikServer@MyCompany.com' Port = '25' SmtpServer = 'smtprelay.MyCompany.com' Subject = 'MyQlikServer Backup Failure' Body = "The backup failed. The error message was: $ErrorMessage" } Send-MailMessage @FailMailParams }
My next steps are:
- Add timestamps to the filename
- Keep the most recent copy and Archive the rest.
- Add qvf file retention
-
Link table 3
Link table example 3 Script: Sales:LOADYear & '_' & Month & '_' & Branch & '_' & [Item Number] as SalesKey,//"Year",//"Month",// Branch,//"Item Numbe... Show MoreLink table example 3
Script:
Sales:
LOAD
Year & '_' & Month & '_' & Branch & '_' & [Item Number] as SalesKey,
//"Year",
//"Month",
// Branch,
//"Item Number",
"Customer Number",
"Invoice Number",
"Order Number",
"Salesman Number",
"Invoice date",
"Sales Amount",
"Sales Qty",
"Cost Amount",
"Margin Amount",
"Unit of Measure"
FROM [lib://DataFiles/LinktableExample.xlsx]
(ooxml, embedded labels, table is Sales);Inventory:
LOAD
Branch & '_' & [Item Number] as InvKey,
//Branch,
//"Item Number",
"On Hand Qty"
FROM [lib://DataFiles/LinktableExample.xlsx]
(ooxml, embedded labels, table is Inventory);Purchasing:
LOAD
Year & '_' & Month & '_' & Branch & '_' & [Item Number] as POKey,
//"Year",
//"Month",
//Branch,
// "Item Number",
"PO Number",
"Req Delv Date",
"PO Amount",
"Ordered Qty"
FROM [lib://DataFiles/LinktableExample.xlsx]
(ooxml, embedded labels, table is Purchasing);/* Create Link Tables */
//Sales
LinkTable:
Load DISTINCT
Year & '_' & Month & '_' & Branch & '_' & [Item Number] as SalesKey,
Branch & '_' & [Item Number] as InvKey,
Year & '_' & Month & '_' & Branch & '_' & [Item Number] as POKey,
Year,
Month,
[Branch],
[Item Number]
FROM [lib://DataFiles/LinktableExample.xlsx]
(ooxml, embedded labels, table is Sales);//Inventory
LinkTable:
Load DISTINCT
Null() & '_' & Null() & Branch & '_' & [Item Number] as SalesKey,
Branch & '_' & [Item Number] as InvKey,
Null() & '_' & Null() & Branch & '_' & [Item Number] as POKey,
Null() as Year,
Null() as Month,
[Branch],
[Item Number]
FROM [lib://DataFiles/LinktableExample.xlsx]
(ooxml, embedded labels, table is Inventory);//Purchasing
LinkTable:
Load DISTINCT
Year & '_' & Month & '_' & Branch & '_' & [Item Number] as SalesKey,
Branch & '_' & [Item Number] as InvKey,
Year & '_' & Month & '_' & Branch & '_' & [Item Number] as POKey,
Year,
Month,
[Branch],
[Item Number]
FROM [lib://DataFiles/LinktableExample.xlsx]
(ooxml, embedded labels, table is Purchasing);Hope this help
Vikas
-
QAP (Qlik Analytics Platform)
Hi All, As we all know Qlik Analytical Platform is quite new in market. People are still in confusion as what QAP is, how it is different than Qlik ... Show MoreHi All,
As we all know Qlik Analytical Platform is quite new in market. People are still in confusion as what QAP is, how it is different than Qlik Sense Enterprise. How to configure QAP Server, Manage QAP and especially what exactly the output of QAP. There are lots of tickets which give you chunk of information like difference between QAP and Qlik Sense Enterprise, how to reload on QAP server when there is no HUB under QAP but there is nowhere how to configure a QAP solution from scratch to end with details.
This document guide you to know what QAP is , how it is different than QSE, setup QAP Server, create and publish Mashup(Embed Analytics ) a, manage QAP Objects and reload application on QAP when there is no HUB at all.
Question: Why Qlik Sense client needs QAP ?
Answer:
- QAP is for external users simply which doesn't need self service or QSE capabilities.
- DMZ network where external users are not allowed to access application from internal network.
- Client doesn't want to buy hundreds of Qlik Sense tokens for such users which don't need deep analysis.
- No certain details of Qlik Sense end users like users count, user details(anonymous).
- Embed Qlik Sense objects in client’s web portal like any news paper, public portal etc.
QAP setup needs knowledge of Qlik Sense Enterprise (server configuration with security rules) and web developer skills (css and java script and basic HTML) who will help you to embed Qlik Object to web portal.
I have attached one QAP product presentation, you may start with it then below tutorial video so you have enough functional knowledge of product.
Important video tutorial of QAP:
Let’s start from beginning:
Difference b/w Qlik Sense Enterprise and Qlik Analytical Platform:
In principle QAP and QSE is same but in general QAP is subset of QSE means QSE without HUB(No Self Service). QAP is a solution of embed analytics. If no self service is there in QAP it means no "create things" but still developers can create visuals by APIs.
Https://HostName/hub you can view the application but when you open it, it will give you an error you don't have access pass, it means you can't view any apps object on hub.
QAP enables you to display sheet or charts into your website.
Commercial difference, QAP has core based license model unlike QSE which has token based license model (It means no user and login passes, which means you just need to grant the access to users or authorized users to the content which is embedded in web portal )
Recently Qlik introduced core bases license for self service too.
QAP is intended for using our APIs (Extensions, Mashups etc.) to deploy custom solutions.
The Qlik Sense client is disabled in QAP (when you will login to hub you would view the streams and apps but when you will click on any app, it will give you error of access passes).
It has same installation package as QES but applying a QAP license key will disable hub it means you can view the hub, streams list, and application icons but apps objects like sheet, charts, measures and dimensions will not open on HUB. It will give you an error, you hve no access passes.
QAP license policy: when a client buys a QAP token, it comes with minimum 4 cores, it means no matter how many cores or virtual processors are available on deployment server, it will only consume or utilize 4 core among all available cores. So, let’s say client's server has 8 cores or 16 logical processors on Qlik Sense Enterprise server and your application size is 1 GB and you are planning to use same application on QAP, So when you import this application to QAP, It will not work as it works on QSE sever cause QAP(as per the license capacity) will consume only 4 cores out 8 cores and your 1 GB application need 8 cores to perform or load objects like QSE.
Note: Do not be confused, what will happen when you will activate the Qlik Server with QAP license, you will get everything same i.e. QMC as Qlik Sense Enterprise but there will be no Tokens means you can't create any user or login pass which we use to authorize users to access the Qlik Sense content.
To implement QAP server, configured it security with custom properties so you would manage security of data for users with
https://community.qlik.com/docs/DOC-18066
These 9 points will create a stream, import the application and publish the application to the newly created stream and grant access to application and app objects.
Try to use Dev-Hub to view the object i.e single configurator.
Now, we will add one more new security rule which will grant all the users to access the APIs like extensions, mashups, Widget, Single configurator.
***You may create one new custom property QAPExtension with resource type Extension and User and the create a new security rule with Extension Template and write a security rule as:
((User.@QAPExtension=resource.@QAPExtension))
Then go to the extension by Click one start => Extensions => select your extension(Mashup) => go to custom properties and click on the Space bar and select the value you have given while creating Custom properties do the same exercise for users too .
Now, we don’t need to create any custom property because QAP is for all users (no limit) above activity started with *** is when you created a mashup and then you need to provide access of some particular Extension(Mashup) to any specific users.
So, for QAP, write a simple security rule as with template Extension:
((user.name="*" or resource.name="*"))
Which means all user will access the all the APIs (extensions, mashups, Widget, Single configurator).
Often, People ask what if my client hasn't any web portal so in that case above Mashup solution will work, Mashup is kind of website(.html file) or web solution.
This is best with Qlik comes Sense as we can create a HTML page with our Qlik Objects so easily(Basic HTML and JS and CSS knowledge requires).
Now point comes, if all user will come how to secure the data, here comes the real QAP solution,
Generally what QAP says, create an application on Qlik Enterprise or Desktop, import on the QAP server and publish its Objects to client website (Mashup is a HTML page but it’s not a web portal).
So above, you have imported an application, created stream, published application, granted access to all users. What is left now, go to single configurator, select one object, there will be two links come on top side, Iframe and URL, Copy anyone and go to the website page where you need to show these objects and paste it(Please take the help of web developer because every object needs a proper space and alignment).
If any user who is not authorized to view Qlik objects but has rights to login web portal where Qlik Sense objects embedded then it will show an error, so you may ask your web designer to handle this case and put any image instead of INVALID OBJECT message from Qlik Sense.
Now when any user will come it will see an error, access denied or ask for credentials , so You may configure the single sign on here ,Please refer the very useful document for single sign on :
Last Point, how to manage the QAP server application. I mean, reload and all. So, You can’t open any application on QAP HUB and there will be no script editor so how to reload if you have imported the application from Qlik sense enterprise:
If you have imported the apps from another Qlik Sense Server the required data connections are unfortunately not imported together with the qvf-file. That is probably why the tasks fail since the data connections are missing. Check in QMC > Data Connections if they are there and if they are properly set up.
Please refer this article to reload data in QAP: https://qliksupport.force.com/articles/000025412
” When you import an app developed on Qlik Sense Desktop, existing data connections are imported to the QMC. When you export an app from a server, existing data connections are not exported with the app.”
Since the QAP is a Qlik Sense-server without the "hub" and aimed for integration purposes only you have to find a way to import the data connections into the QAP QMC for reloads to work. One way could be to create an empty app in Qlik Sense Desktop with the required data connections and then import this qvf into the QAP. Then you can manage and set up the imported data connections properly in the QAP QMC.
Reach to me for further assistance, Please drop an email to kumar.rohit1609@gmail.com
Add me on Linked In : https://in.linkedin.com/pub/rohit-kumar/2b/a15/67b
Add yourself on Qlik intellectuals page on Facebook at https://www.facebook.com/QlikIntellectualsPlease like this page or add as bookmark and add your ratings so my I will know how effective my solution is for Qlik Users.
You may add your queries below so I would come up with solution. I will try to keep update this document with more powerful inputs. Your queries and inputs will help me to improve the quality of this solution. The aim is to make a perfect document which will guide you to a perfect solution.
-
Working days and hours calculations for custom weekends
Previously, I created a document for calculating working hours between two dates, excluding weekends (Saturday and Sunday) and holidays, using the net... Show MorePreviously, I created a document for calculating working hours between two dates, excluding weekends (Saturday and Sunday) and holidays, using the networkdays function. Please refer to the link below.
Working-Hours-Calculation-excluding-weekends-and-holidays
However, what if the weekends are not Saturday and Sunday (e.g., Thursday and Friday)? Or if only Sunday is considered a weekend, not Saturday? In such cases, the networkdays function won't be applicable. While there are many ways to address this, I will demonstrate one approach in this post.
I have detailed all scenarios step by step in the attached Word document and included example QVF files. This code is compatible with QlikView as well.
The logic described here can be applied to any defined weekends, but for regular weekends (Saturday and Sunday), I recommend using the built-in networkdays function for working day calculations. For working hour calculations, please refer to my previous document linked below.
Please feel free to share any suggestions.
-
Cyclic Groups in Qlik Sense
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 workarou... Show MoreThis 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
- Add a variable vDimension and set it to Year.
- 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
- Add a variable vButton and set it to Sales
- 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') - 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-2There are several proposals for cyclic groups, thanks for the inspiration:
Juraj Misina, https://dataqlues.com/getobjectfield-function-and-alternate-dimensions/
-
Qlik Explorer for Developers
Qlik Explorer for Developers is a light weight tool designed to provide a simple way for developers to examine the contents of a Qlik Sense server. T... Show More -
ChatGPT in Qlik Sense
ChatGPT , developed by OpenAI, is the subject of the moment and is already the 4th most used "search engine" in the world, surpassing Bing and Du... Show More -
SaaS Enablement (Part 2 - Mashups from the Cloud? Yeah we've got that)
As you know one of the wonderful things about Qlik Sense is that it can be consumed in the native UI or inside of Mashups. I want to assure you that n... Show More -
link table
Link Table : Please find simple link table concept implemented with multiple fact tables. Hope definitely help new comers will help to clear concept o... Show MoreLink Table :
Please find simple link table concept implemented with multiple fact tables. Hope definitely help new comers will help to clear concept of link tables.
Data Modelling is technique in qlik for linking tables we can use link tables concept please refer ppt .
Qlik Tips: Rules for creating a Key/Link Table in QlikView and this post
ref: https://www.analyticsvidhya.com/blog/2014/12/remove-synthetic-key-concatenation-link-table-qlikview/
Please find attached excel & data model containing how to implement a link table.
Attached Solution Linktable2
Hope this help to new comers.
Thanks
Vikas
-
Effective use of Value List for Measures as a dimensions
Hi Team, We can make use of Valuelist effectively to show KPI's row wise as a custom report in Qlik Pivot table sample attached for ready reference. ... Show MoreHi Team,
We can make use of Valuelist effectively to show KPI's row wise as a custom report in Qlik Pivot table sample attached for ready reference.
Hope this helps.
Vikas
-
Qlik Sense App: Create Test Data For Correl Function Using AutoGenerate
A common use case in Qlik Sense is to generate some test data in order to demonstrate a function or to run some testing. The AutoGenerate function is ... Show MoreA common use case in Qlik Sense is to generate some test data in order to demonstrate a function or to run some testing. The AutoGenerate function is a great way of getting a large number of rows with some made up data to test or demo over.
Recently I was asked to implement a Correlation Coefficient and having not used the correl function in Qlik before I wanted to check that it gave sensible results before putting it into a live app (spoiler: it works fine). To do this I used AutoGenerate to give me X values from 1 to 1,000 and then various Y values to give positive, negative and non correlated data sets.
This app gives the various views of the correlation coefficient, but more importantly it shows how to generate test data, including random values, random selections of dimensions and generated dummy IDs.
The app is accompanied by a blog post, which can be found here:
https://www.quickintelligence.co.uk/generating-test-data-in-qlik-load-scripts/If you have any comments or suggestions of other ways you can autogenerate test data please comment on this document, or on the comments on the blog post.
Cheers!
Steve
-
Heat Map Country->State->District Wise for INDIA
Hi All, Created simple application State->District wise layer map using KML. Application used container object which has ability when you click on s... Show MoreHi All,
Created simple application State->District wise layer map using KML. Application used container object which has ability when you click on state automatically further drill down district will be display for India country.
Hope this will be useful for new comers who wanted to learn google map using layer area option.
Thanks
Vikas
-
Qlik GeoAnalytics Connector Tutorial
A list of all Operations and Connections available through the Qlik GeoAnalytics Connector A more in depth analysis, including Use Cases and step by s... Show MoreA list of all Operations and Connections available through the Qlik GeoAnalytics Connector
A more in depth analysis, including Use Cases and step by step instructions in setting up the following Operations, with screenshots:
- Routes
- Closest
- Dissolve
- Travel Areas
- Binning
- Cluster
- Within
- Loading a Shape File
- Loading a CAD file
- Converting from Northings/Eastings to Lat/Lon
-
Multiple lines in Pivot and Straight table cells in QlikSense
[Paragraph format. Descriptive text here. See Content Guidelines for details. You can remove sections that are not needed for your article. If subhead... Show More[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