Hi Qlikers,

Sometimes we can have requirement to calculate the business working hours between two Dates due to certain reasons. There are many solutions available on community but I thought let's create the document so that everyone can utilize this.

Consider the below case

Let's say one Ticket is logged into the system on certain day , call it as Start Date, and the same Ticket got resolved on certain day, call it as End Day. Now we may want to calculate the working hours between these two Dates to calculate the efficiency of ticket closure time.

1) Calculate business working hours excluding week ends(Saturday and Sunday) and Holidays.

Below are the considerations

1) Count only standard working hours ( 9 AM - 6 PM)

2) Exclude Saturdays and Sundays

3) Exclude Holidays

Below is the Setup in QlikView

You can change the Date format of the below variables as per your convenience eg. 'DD/MM/YYYY hh:mm:ss' to 'DD-MMM-YYYY hh:mm:ss'

SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss';

First, convert your Timestamp date format to 24 hour format using the below functions

a) Timestamp# ()    (if your Timestamp values are text) 

b) TimeStamp ()     (If your Timestamp values are in proper Timestamp format)

Now you can setup the below variables for standard working hours in weekdays.You can change the Variable according to your working hours (Here 9 AM - 6 PM) and rest calculation will be done automatically

Now Setup the Holiday list as below. You can use your own holiday list

Below is the logic to calculate the business working hours between to dates

Here is the output

2) Calculate business working hours excluding week ends(Saturday and Sunday), Lunch Breaks and Holidays.

Below are the considerations

1) Count only standard working hours ( 9 AM - 6 PM)

2) Exclude Saturdays and Sundays

3) Exclude Lunch Break (1 PM - 2PM)

4) Exclude Holidays

Set the Variables for standard working hours and lunch breaks. You can change the values according to your needs

Include the Holidays

Below is the logic to calculate the business working hours between two dates

Here is the output

Please see the attached QVW.

Feel free to provide any suggestions.

Regards,

Kushal

This page will collect all relevant release information about Power Tools for QlikView, and will be continuously updated with each new release and with any patches that are issued. This way we hope for a more consolidated view of the Power Tools suite.

Do you have a feature request or a bug?

• Start a discussion in the QlikView Management or Deployment forums, or
• Email sbn@qlik.com

Latest release

Power Tools 1.3.2 for QlikView

Power Tools 1.3.2 for QlikView - Release Notes

Power Tools 1.3.2 for QlikView - Readme

QlikView 12 compatibility matrix

Please note that this matrix only describes the compatibility with QlikView 12. It does not indicate whether or not Power Tools are officially supported, because they are not.

SharedFileViewer

Provides insight into the QlikView Server .shared files belonging to .qvw documents. The .shared file stores server data such as server/shared bookmarks, server objects (charts and objects created by a client via the server), annotations and other data that is specific to the document. The tool visualizes the content of the binary .shared file and provides options to "Repair" legacy files, as well as defrag large .shared files.

QvServerObjectConnector

A custom data connector for QlikView that much like the SharedFileViewer provides insight into .shared files, but instead allows to load data straight from .shared files into QlikView documents for analysis. The tool supports extraction of data like ownership, size and type of objects, expressions, annotations, bookmark selections (fields and values) and much more.

QlikView Server Agent

A small service utility that allows easy management of QlikView services. Also allows for quick change of credentials across multiple services at the same time.

QlikView Server Super Agent

A monitoring tool for QlikView Server, that monitors Qv services regardless of version (9+) and reports any service outage with email notifications and/or log files.

QMS API Client

A highly useful tool that gives its user the possibility to interact with the complete setup of QMS API functions in QlikView Server without typing a line of code. Visualizes data structures and enables the user to parameterize and test every function call based on their own data or data from the QMS API.

Qv User Manager

A command-line tool to list, add, remove and edit CAL and user information in a QlikView Server remotely.

QvsDetector

Scans the current subnet for other QlikView Servers and visualizes information like license, root folder, version and such. Also enables certain administrative functions.

Reload Schedule Migration Tool

A small step-by-step instruction tool that helps migrate reload schedules from non-Publisher databases in 9 to Publisher databases in 10 or 11, by manipulating the QVPR database (XML repository required) directly.

Server Object Handler

Enables listing, ownership changing and deletion of server objects in documents hosted on a QlikView Server remotely. Also allows ownership changing to be performed on a selected number of server objects matching a given name pattern.

Server Object Handler Batch

The command-line little brother of the Server Object Handler, that does all the same, but from any command prompt or batch script.

XmlDbViewer

Visualizes and enables searching in and editing of the QlikView Publisher Repository database (QVPR), when in XML format.

Shared File Repair

Uses the upgraded .shared file verify and repair functionality that was re-introduced in QlikView Server (QVS.exe) SR5, by providing a GUI to execute verify/repair operations on one or more .shared files and report the result. Note that even though the verify/repair functionality first came into QVS in 11.20 SR4, this version requires the upgraded functionality in 11.20 SR5.

Frequently Asked Questions

Q: What is a Power Tool?

A: A Power Tool is a small utility program or application that aid in performing a specific functionality or that extends QlikView functionality in some other way.

Q: When should I use a Power Tool?

A: Power Tools should be used when there is a need for functionality that is not in the QlikView product suite already, to evaluate new functionality or when administrative actions outside that is not available as part of the regular program suite might be needed.

Q: Do I need to install any of the tools?

A: No, all tools are, as of this moment, drop-to-deploy tools. Be aware that some of them require resource files outside of the executable itself, like for example configuration files or DLL files. When deploying, make sure to deploy the whole folder and its content.

Q: Do the tools require administrative privileges?

A: Some of them require this, yes. It is up to each tool and its functionality. If you are using UAC (User Account Control) security, make sure to run the tools in privileged mode (right-click and select “Run as Administrator”).

Q: Is there any documentation for the Power Tools?

A: Yes. A limited documentation for the Power Tools is included in each of the separate tools.

Q: Are the Power Tools Supported?

A: No. The Power Tools are not supported and is seen as use-as-is tools, supplied separate from the QlikView product suite. *** Qlik is not responsible for any consequences such as environmental issues, data corruption, configuration errors or unexpected behavior, resulting from the use of Power Tools. ***

Q: Where do I report bugs in the Power Tools?

A: To report a bug, please visit our QlikCommunity Discussion Forums, for example the QlikView Management or QlikView Deploymentforums or send an email to SupportServiceabilityLabs@qlik.com. Bugs will be fixed between releases of the Power Tools entirely at Qlik's discretion.

Older versions

Power Tools 1.3.1

Power Tools 1.3.1 for QlikView

Power Tools 1.3.1 for QlikView - Release Notes

Power Tools 1.3.1 for QlikView - Readme

Power Tools 1.3.0

Power Tools 1.3.0 for QlikView

Power Tools 1.3.0 for QlikView - Release Notes

Power Tools 1.3.0 for QlikView - Readme

Power Tools 1.2

Power Tools 1.2 for QlikView

Power Tools 1.2 for QlikView - Release Notes

Power Tools 1.2 for QlikView - Readme

Power Tools 1.1

Power Tools 1.1 for QlikView

Power Tools 1.1 for QlikView - Release Notes

Power Tools 1.1 for QlikView - Readme

Power Tools 1.0

Power Tools 1.0 for QlikView

Power Tools 1.0 for QlikView - Readme

Hi Folks,

This document provide steps to fetch the google spreadsheet information and use in qlikview by using "Qlik Google drive & Spreadsheet connector".

Hope this will help the beginner to use it.

Hello all Qlikkies!

QlikView Governance Dashboard: provides QlikView customers with an easy way to determine how compatible and convertible their QV apps are to Qlik Sense -- using a customizable "Sense Profile Score" that assists them in their planning and implementation efforts to migrate from QlikView to Qlik Sense. The App include new "Sense Profile" sheet with tables showing 

• Sense Profile Score: Composite score of weighted User Adoption, Object Density, and Object Compatibility scores. 
• User Adoption Score: How much is each app used and how recently? (Graded usage count for past 90 days.)
• Object Density Score: How many sheet objects per sheet in the QV App? (Over 12 can be difficult to re-create in a Sense app!)
• Object Compatibility Score: How many QV sheet objects in each app are not compatible in Sense?

Additional features included:

• Unused Sheets by app to indicate which sheets should be considered for removal before re-scoring and prioritizing for migration to Sense.
• Custom Configuration of profile scoring -- threshold and relative weight of each component of the Sense Profile Score.

Qlik Sense License monitor: Allows Qlik Sense Administrators ability to holistically monitor their analyzer & professional license consumption across QlikView & Qlik Sense (windows, eFe & QCS deployments) to answer the following business questions:

• Are we running out of licenses?
• What is the history of usage?

*This needs to be done at a aggregate level without violating GDPR regulations on personal data

Hey there,

I am starting this article to yell out to the community, that I have completed a generic script (suitable for QlikView and Qlik Sense using the Qlik REST Connector) to use the API of Synthesio for loading data.

I am not sure how many folks will search for this combination, so before I document hours of "how to do" here, please ping me a note (reply below or email me at christof.schwarz@gmx.at) that you're interested. I will then share it here. If noone ever asks for this combination, I saved a lot of time 😉

The idea is the following:

• Synthesio offers several REST APIs
• You have to get an accesstoken which is valid for some hours. My script is doing so.
• You can query Synthesio's Mention API, the one you likely look for are
  • the Reach method Synthesio API Reference and
  • the Analytics method Synthesio API Reference#
• The result is a json object, which unfortunately does not translate automatically into a workable datamodel when using the REST Connector wizard.
• I had to load the result basically into one cell (one row) and then parse the content accordingly.

Excerpt from the "magic script":

A QlikView Technology White Paper

Published: November, 2011

www.qlik.com

Introduction

Event Driven Execution (EDX) allows the running of QlikView batch jobs based on external events. The primary usage of EDX is to have an external scheduler or Extract,Transform, and Load (ETL) tool (for example, Informatica) run QlikView batch jobs as part of a larger batch process. There are also other use cases, forexample, triggering a QlikView batch when a file arrives and user-initiated batches. This document describes the requirements for each of these three use cases.

The example code (attached below as QMSEDX-v1.exe) and executable (attached below as QMSEDX_CommandLine_v1.exe) that is delivered together with this document uses the new EDX API, which means this document and the example code are useful both when using the EDX functionality for the first time and when migrating from previous versionsof the EDX API.

Use Cases

Using an External Scheduler or ETL Tool

Many large organizations have other batch requirements than just QlikView. Such organizations often use a standard tool to run all batch processes, so that the IT training costs are minimized and common operations (for example, logging and re-launching) are handled in one central place. In addition, QlikView batch processing is almost always reliant on new data being available and in many cases this data is created by other batch processes. An external scheduler or ETL tool can be used to prepare the data and then run the QlikView batch to load it.

Most scheduler and ETL tools are designed to run external batch programs via a command line interface. The reasons for this include:

• Batch processes are long-running, so starting aseparate process to handle them provides a simple way for the scheduler tomonitor the batch processes.
• The scheduler can be configured to run the batchusing a particular operating system account that provides the required securitycontext.
• If the batch process fails, the standard operatingsystem error handler can be used to pass this information back to the scheduleror ETL tool.

The main alternative to using a command line tool is using some sort of web services. Unfortunately,web calls cannot be used for long-running processes, since a timeout eventually occurs. A common solution to this problem is to start the process with a web request and then follow it up regularly with subsequent requests to find out if the original request has finished successfully or not.

File Arrival Detection

In many IT departments, the inter-system communication is not orchestrated by ETL or even Enterprise Application Integration (EAI) tools. Instead, files are pushed. For example, a non‑QlikView batch job that extracts data from a production database is typically owned by the IT team running the database, which means the team is responsible for creating and configuring the batch job and ensuring that it runs correctly.On the QlikView side, a program that checks if a file has arrived must be running, so that the file can be used in a subsequent QlikView batch.

In this scenario, simply starting the QlikView batch process should be enough. There is no need to follow its execution. The program is typically a Windows service as it runs all the time.

User-initiated Action

TheQlikView Automation API provides a number of functions that run QlikView batch scripts, for example, Document.Reload(). However, it is normally not a good idea to let users run batch jobs, since a QlikView batch job typically replaces the entire data set of an application used by many users.This means a single user can interfere with the work of all the others unless the QlikView batch is carefully configured. Because of this, the batch functionality is disabled in a QlikView Server deployment.

• The marketing department at a pharmaceutica lcompany has an organizational division between the analysts and the product managers. The analysts are in charge of loading a dozen external data sources of varying quality. Once the data relating to a particular product is ready, the running of a QlikView batch loads the data and publishes the application to the relevant product managers.
• A software company with thousands of employees wants each employee to have access to their sales data up to the last 30 minutes. The computer resources needed to support such data usage are massive. Since the salespeople only use the system once a week on average, it is decided to allow each user to run a QlikView batch to generate a QVW (for the user)whenever needed.

EDX in QlikView 11

In QlikView 11, EDX runs through the QlikView Management Service (QMS) API. This is a major change from QlikView 10, where EDX is realized by calls directly to a QlikView Distribution Service (QDS). The QMS API is a web service API that uses theSimple Object Access Protocol (SOAP). Client applications make HTTP (web) requests to QMS on port 4799. The system is secured by NT LAN Manager (NTLM) as well as special protective measures to avoid certain types of hijacking attacks known as “time limited service key”. This combination of security means the client application must be written in .NET and therefore the provided example code is .NET projects/solutions developed in Microsoft Visual Studio 2010.

The client application uses NTLM to authenticate a Windows account to QMS. QMS then checks which Windows groups the Windows account for the client application is member of to determine the function calls the user is allowed to make. Most of the QMS API requires membership in a local group called “QlikView Management API”, but to run EDX, a separate group, “QlikView EDX”, should be used. Both groups are local Windows groups on the server where QMS runs.

The client application makes calls to instruct QMS to start a task and in return receives an error code indicating success or failure, as well as an execution ID. The execution ID uniquely identifies the execution of the task as opposed to the task itself. The client application then periodically polls QMS to check the progress of the task execution. This poll request returns a data structure that contains, among other things, the execution status, start time, stop time (if already finished), and a list of new execution IDs. These subsequent execution IDs represent the execution of tasks that are triggered because the initial task has finished. The client application can then follow an entire set of inter-related tasks that together make up a full parallelized batch flow.

Lastly, the example code outputs log information and returns an error code to the operating system. The error code can be recovered in a standard way, for example, by using the %ERRORLEVEL% environment variable in a BAT file.

Changes Compared to Previous Versions

From a functionality perspective, the following has changed compared to using EDX in QlikView 10:

• Because communication is with QMS instead of QDS, a QDS cluster can be used to load balance EDX calls. If high availability is required for the batch environment, QMS is a single point of failure. Consider either virtualization of QMS or an active/passive system.
• The overall success or failure code of aQlikView batch can be returned. Previously, the status of the task could be fetched (that is, the definition of the batch job), but not the execution ofthe batch job.
• The execution of subsequent tasks can be followed.

Notes on Using the QMS API

Finally,some notes on using the QMS API in QlikView 11:

• The EDX password must not be null. Use an empty string, if no password is configured.
• The QDS ID is required in the function call that starts the EDX batch job. However, higher-level privileges than membership in “QlikView EDX” are required to get a QDS ID. The simple solution is to pass an empty GUID for the QDS ID, which means QMS will search for the task in all configured QDS instances. Almost all projects today only have one logical QDS configured, so this can be a useful solution.
• EDX can be used without a QlikView Publisher license. Simply pass the name of the QVW instead of a task name and the reloadschedule of the QVW will run.

Hierarchies are an important part of all business intelligence solutions, used to describe dimensions that naturally contain different levels of granularity. Some are simple and intuitive whereas others are complex and demand a lot of thinking to be modeled correctly.

• What is the difference between a balanced and an unbalanced hierarchy?
• How can a hierarchy be loaded into QlikView?
• Which data model should be used?
• How do I create an authorization scheme using a hierarchy?
• How can I check for data integrity problems in the hierarchy data?

These questions and others are answered in this Technical Brief. Attached you will also find a sample unbalanced, n-level hierarchy along with the script to load it.

See also the following blog posts:

Unbalanced, n-level hierarchies

Authorization using a Hierarchy

HIC

2013-11-26: Added section on authorization.

Hey,

I hope I can solve a lot of peoples headache with a better way of loading JSON data.

This method works by loading the JSON data into an table with a single field, and then placing it into a variable and then converting the value into xml which then is loaded. Works very well but is still a bit for the technical people. I will not provide any support.

In order to make this work, we need to detect the XML definition after JSON to XML conversion. Open the JSON Loader.qvw Application and the JSON url.

Copy the JSON output into the vData variable, and click the JSON to XML button. Now take the output in the left side and copy that into a .xml file and load it from qlikview.

The output script should look similar to this:

TAG:

LOAD adminCode3,

    adminName2

FROM [Desktop\xml.xml] (XmlSimple, Table is [XML/POSTALCODES/TAG]);

From this we need to extract 2 things. The list of fields (place into vFields), and the XML tree to load (place into vXMLTree).

In the edit script set the variables based on the above:

Let vUrl = 'http://api.geonames.org/postalCodeLookupJSON?postalcode=6600&country=AT&username=demo';

Let vFields = 'adminCode3,adminName2';

Let vXMLTree = 'XML/POSTALCODES/TAG';

Cross fingers and reload

Do this for each JSON source.

Enjoy Torben Seebach 4C Management Consulting

Hello everyone!

Higher management often requires specific data delivered to their mailbox every morning. They are not interested in interaction, using filters and “playing” with data. They just want their predefined Excel ready when they will turn on the PC. Of course great solutions like Qlik NPrinting are available, but there is a way to do it even with QlikView Personal Edition.

This document describes a methods for:

- Automated QlikView document reload

- Automated data extraction from QlikView to Excel file.

- Automated distribution of this Excel file to a specific recipient, using free Gmail account

- Scheduling and fully automating whole process with Windows Task Scheduler

You can find all files in the attachment.

TLDR: Learn how to fully automatically reload, extract and send data by email using Gmail account!

All inputs are welcome. I hope someone finds it useful!

PS. This document is based on one of mine QlikView blog posts.

BR,

Kuba

"How to discover your data in a fast dynamic way?"

When starting a new project the first thing I want to do is get a feel for the data. Preferably in a fast and dynamic way. You can use the "Advanced Data Discovery module" to tackle this problem. The Advanced Data Discovery module consists out of 4 dynamic list boxes and a dynamic table which comes in handy when modeling a project, checking data quality, testing the interaction between fields or exploring your data.

Screenshots:

Advanced Data Discovery sheet overview

Dynamic list box with field statistics

Benefits:

• Useful to check interaction between fields while creating your data model
• Dynamic Data Discovery
• Field statistics based on current selections or based on the complete set
• Fast data quality checks
• Dynamic list box

Cons:

• A script reload is required

PS: Special thanks to the creator of the first version of the dynamic list box from who I could not trace back the blog post I encountered 3 years ago.

The Advanced Data Discovery module is now part of the QvM - Qlikview Modules template.

Thanks for your feedback and comments.

Have fun Qlik'n, Koen

‌QlikViewModules.com

Hi everyone,

Here is a file to write the content of a QVD into an MS SQL database with minimum amount of coding.

The script uses bulk insert function in SQL and inserts 1,000 rows at a time.

There are instructions/assumptions in the front-end of the app.

1. Ensure the source QVD has the columns correctly formatted on the first run (as it creates the SQL table based on the coded Qlik data types - even though Qlik data types technically don't exist) as this determines the 'data types' it builds into the SQL table.
2. Replace the existing "CONNECT TO" string to the database server.
3. Point to the QVD you want to write to SQL.
4. Script will change/replace all special characters in data, table and field names. Underscore will replace any 'space' in table and field names and the latter 2 will be in 'all caps' to follow DB naming-convention.
5. Script will create a table in the database (the database itself must exist or be created prior to launching the script).
6. Re-run the script after the script has created the target SQL table and the script will (bulk) insert 1,000 rows at a time.
7. File creates/updates one "DB_Write_Log.qvd" QVD with stats of what has been written to the SQL table by this script/file (keeps a historical record).
   

Tentative stats are: (a) Writes 1,000 rows (one insert) every 1.63 seconds for a table 7 columns deep; (b) Writes 45,637 rows in 58 seconds for a table 10 columns deep.

Hope you find this useful.

A video tutorial here: Qlik Bulk INSERT into SQL table - YouTube

Have fun,

C  ;o)

This tutorial presents a script pattern for assigning time dimensions to multiple fact dates in a data model. It answers the commonly asked Forum question "how do I link to two dates"?

The pattern will demonstrate how to link all fact dates to a common calendar as well as using separate calendars for each fact date.

EDX is a trigger mechanism for starting a QlikView reload or task from an external source, such as a scheduler or application. To start a task using EDX the QlikView Management API must be used and this is delivered in the form of a Web Service.  The full details on EDX in QV 11 plus a detailed example and code is available here :  http://community.qlik.com/docs/DOC-2650

Below is a step by step simple example which may help you start to develop something for your own needs.  For this example you will require and need to be familiar with Visual Studio (the free version is fine, although the attached project is for Visual Studio 2010 pro) to write your application and you will need to under stand how to use QlikView management API. 

Step 1 - Create a task

First of all create the task you want to trigger, this can be any type of task and do this using the QlikView management account as normal.  For the task create a trigger, and for the type of trigger chose "On an external Event" and you need to specify a password that will be used to call the task (this isnt used anywhere else except for starting the task).  The below works both if you have a fully license Publisher but also if you just have server but the task creation screen in the QMC is slight different for each.

Step 2 - Create your application to us the API

We are going to use the QlikView management API to build an application that will start the task, the application itself could take any form such as web page with a button (like the attached) a command line app, a web service; whatever suits your business needs. The attached example is build as a .net web page with a button a user could click but the code can be lifted and moved to another application type. 

Its worth point out that this is the bare essentials for using EDX, it does not include things like task monitoring or error handling,  examples of more complex features can be seeing the above referenced document.

The attached project is pre-configured to use the QliKView Management API and below is just the code required to start a task,  refer to the documentation on how to get started with using the API as this can be a bit tricky to get used to.  I have used C# on a asp.net page and many examples will use this but you can use other languages and technologies also.

Open the attached project and you can review the key lines in the project by looking at the following:

First we will declare some variables we need to use,  the address of your QlikView server,  the name of your task (or its ID), and the password you specfied in the task.  Typically you would want to put these in a config file or allow them to be passed as parameters.

            string QMSURL = "http://localhost:4799/QMS/Service";

            string Taskname = "Reload of Golf Quest.qvw";

            string Taskpassword = "secret";

Second we will need to handle the Service Keys used to talk to the API, this needs to be done to call any function in the API, refer to docs for more details on this

            QMSClient Client;

            Client = new QMSClient("BasicHttpBinding_IQMS", QMSURL);

            string key = Client.GetTimeLimitedServiceKey();

            ServiceKeyClientMessageInspector.ServiceKey = key;

Finally the real part of the code example, calling the task.  It can be as simple as just one line as shown below or more complicated. 

            Client.TriggerEDXTask(new Guid("00000000-0000-0000-0000-000000000000"), Taskname, Taskpassword, "", new string[0]);

Breaking this line down the function "TriggerEDXTask" has 5 parameters

1. The GUID of the QDS - this isthe identity of the reload component in QlikView, this might be the basic one attached to a server or a full Publisher, its actually the same service.  With publisher the GUID might be something different to the blank one above however the above line should work in both cases. If you have them on different servers, or more than one publisher you need to give the full and correct GUID.
2. Taskname or GUID - fairly self explanatory the name of the task in the QMC, or its ID
3. Task Password is the password you specified in the task
4. Parameter name - When starting the task you can pass a variable into the reload script if needed, ive left it blank in this example
5. Parameter values - the values for the above.

OK thats it, of course there is plenty more that could be done here but this is the basics. It is also possible to monitor the task for completion.

Step 3 Security & Running your code

To be allowed to request something from the API, the user or the code calling the function needs to run as an account with particular permissions.  These are windows permissions defined on the QlikView Server as two local groups, users must be in either the QlikView Administrators or QlikView EDX groups. The later of these is not created by default.

Depending on what type of application you build the way the users name gets to the Management Service can vary.  If the code is built into a windows exe file this will run as the user currently logged into windows. If it runs as a web page it will by default run as the identify of the application pool in IIS depending on it is set up and this might not be correct. 

The attached example uses imponsation, that is that the ID of the user logged into the web page will be passed along to the QlikView API.  You can see this being turn on in the web.config file with the line  <identity impersonate="true"/>.  IIS needs to be configured to allow this to work by requiring windows authentication on the page and for the application pool to run in classic mode.  In this scenario every user who might call the function needs to be in the groups mentioned above, while this is quite granular it might be administation you dont want to have. Also if the application is being called by a non-windows source such as a Unix based scheduler this may give issues.

An alternative is to modify the code so that a single user name is used to connect to the API on the behalf of all users, of course you needs to factor in some way of ensuring only permitted users can then use the application.  To do this modify the code as follows.

After the line Client = new QMSClient("BasicHttpBinding_IQMS", QMSURL); place the following lines

            WindowsClientCredential creds = Client.ClientCredentials.Windows;

            creds.ClientCredential.UserName = "myadminaccount";

            creds.ClientCredential.Password = "mypassword";

This will now call the API using the hardcoded credentials, although these should be saved in a config file.

Good luck and i hope this example helps

HI All,

The below script helps in creating the Master Calendar with Working & Non working (Holiday) flags.  Holidays are loaded from the Excel file.

LET vWeeklyHolidays = 'Sat;Sun';   // Adjust weekly holidays here, currently considered Saturday & Sunday as holiday, based on your requirement change it accordingly

CalendarMaster_Temp:
LOAD
    Date(InvoiceDate) AS InvoiceDate,
    Year(InvoiceDate) AS Year,
    'Q' & Ceil(Month(InvoiceDate) / 3) AS Quarter,  
    Month(InvoiceDate) As Month,
    Day(InvoiceDate) As Day,
    WeekDay(InvoiceDate) AS WeekDay,  
    Week(InvoiceDate) As Week;
Load
     Date(MinDate + IterNo() -1 ) AS InvoiceDate  // Replace InvoiceDate with your actual date field name
While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load
    Min(InvoiceDate) AS MinDate,
    Max(InvoiceDate) AS MaxDate    // Replace InvoiceDate with your actual date field name
RESIDENT Invoice;                  /// Replace Invoice with your actual Transaction Table.

LEFT JOIN(CalendarMaster_Temp)
LOAD Date(Holiday) AS InvoiceDate,
     Description AS HolidayDescription,
     1 AS IsHolidayFlag
FROM
[List of Holidays.xlsx]   // Load holiday details from Excel file attached, if you have this info in database tables change it accordingly
(ooxml, embedded labels, table is Holidays);  

Calendar:
LOAD
  InvoiceDate,
    Year,
    Quarter,  
    Month,
    Day,
    WeekDay,  
    Week,
    HolidayDescription,
  If(SubStringCount('$(vWeeklyHolidays)', WeekDay(InvoiceDate)) OR IsHolidayFlag = 1, 1, 0) AS IsHolidayFlag,
  If(SubStringCount('$(vWeeklyHolidays)', WeekDay(InvoiceDate)) OR IsHolidayFlag = 1,'Non Working/Holiday', 'Working Day') AS DayType
Resident CalendarMaster_Temp;

DROP TABLE CalendarMaster_Temp;      

Regards,

jagan.

A common metric with a business is to compare  current period with previous period a over same number of days.

few fall into mistake of comparison of current period with the whole previous period.  Take for example Month To Date Sales compared with Previous Month.   If the current date is 15 of the month the comparison is made over 15 days of the current month with 30 days of the previous month and this is not apple to apple comparison and the result will be on always down until reach a day of 30.  To overcome this I have created a master calendar to overcome the period comparison for WTD,MTD,QTD and YTD flags along with previous period Flags.

   if (TempDate >= monthstart($(vToday),-1) and TempDate <= monthstart($(vToday),-1)+ $(vToday)-monthstart($(vToday)) ,1,0) as PrMTDFlag,  //Prior Month Flag

in this Prior Month Flag is set over only same number of days for the current period.  Same follows for MTD vs PrMTD, QTD vs PrQTD and YTD vs PrYTD

The Script for creating an apple to apple Period Flags :

MinMax:

LOAD

  Min([Sales Date]) as MinDate,

  Max([Sales Date]) as MaxDate

RESIDENT [Sales];

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

LET vToday = $(vMaxDate);

//LET vToday = Num(Today(0));

/*************** Temporary Calendar *************

Generates a single table with one field containing

all existing dates between MinDate and MaxDate.

*/

//

TempCal:

LOAD

  date($(vMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE

  $(vMaxDate) - $(vMinDate);

DROP TABLE MinMax;

/*************** Master Calendar ***************

Disconnected during the Date Island exercise by renaming TempDate as IslandDate

*/

SalesCalendar:

LOAD

// TempDate AS CalDate,

    TempDate AS [Sales Date],

  'Week-' & Week(TempDate) AS WeekDesc,

  Year(TempDate) AS Year,

  Month(TempDate) AS Month,

  Day(TempDate) AS Day,

  Weekday(TempDate) AS WeekDay,

   'Q' & ceil(month(TempDate) / 3) AS Quarter,

  makedate(year(TempDate),month(TempDate)) as MonthStartdate,

  date(rangemin(dayname(monthend(TempDate)),today())) as MonthEnddate , //Creating the field MonthEndDate

  Date(monthstart(TempDate), 'MMM-YY') AS MonthYear,

  Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

    InWeekToDate(TempDate, $(vToday), 0) * -1 AS CurWTDFlag,

    InWeekToDate(TempDate, $(vToday), -1) * -1 AS PrWTDFlag,

    InMonthToDate(TempDate, $(vToday), 0) * -1 AS CurMTDFlag,

    if (TempDate >= monthstart($(vToday),-1) and TempDate <= monthstart($(vToday),-1)+ $(vToday)-monthstart($(vToday)) ,1,0) as PrMTDFlag,  //Prior Month Flag

  inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,

    if (TempDate >= YearStart($(vToday),-1) and TempDate <= YearStart($(vToday),-1)+ $(vToday)-YearStart($(vToday)) ,1,0) as PrYTDFlag,  //Prior Year Flag

    InQuarterToDate(TempDate, $(vToday), 0) * -1 AS CurQTDFlag,                                                                   

    if (TempDate >= QuarterStart($(vToday),-1) and TempDate <= Quarterstart($(vToday),-1)+ $(vToday)-QuarterStart($(vToday)) ,1,0) as PrQTDFlag  // Prior Quarter Flag

Then YTDSales =

sum({$<CurYTDFlag ={1},Year=,Quarter=,Month=,WeekDesc= >}  [Sales Amount])

PrYTD Sales

sum({$<CurPrYTDFlag ={1},Year=,Quarter=,Month=,WeekDesc= >}  [Sales Amount])

and YTDCompassion =

num(  ( $(YTDSales ) - $(PrYTD Sales ))  /  $(PrYTD Sales ) , '#,##0%')

This way is more efficient than having a complex set analysis by adding these line to the Master Calendar and setting flags.

Hi All,

This document illustrate how to generate the future dates/months  based on different interval for each records.  i.e. for different keys you need to generate different due dates , consider the e.g. below for Key 1 we need the future due date to be generate every 6 months and for Key 2 we need 3 month follow up  ..

Input Data:

*For key 1 we have Month Interval as 6  so we need to dynamically generate future due date months i.e.

OUTPUT for Key 1:

Script:

DATA:
LOAD Key,
[Month Interval],
Amount,
[Due Date],
[Expire Date]
FROM
[C:\Users\avinash5\Desktop\Sample Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join

LOAD
*,
AddMonths([Due Date] ,([Month Interval]+ ([Month Interval]*IterNo()))) as [Future Due Date]
While
AddMonths([Due Date] ,([Month Interval]*IterNo())) <= [Expire Date]; //Defines when the Interval should end

LOAD Key,
[Month Interval],
Amount,
[Due Date],
[Expire Date]
FROM
[C:\Users\avinash5\Desktop\Sample Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Note: I have fixed Expire date , you could use different dates also.

Regards,

Avinash R

Hi,

I just wanted to share another example on how to create a Gantt chart in QlikView. To do this we can use some of QlikView's available resources as an alternative to the use of Extension Objects. In this case I have used a Pivot Table.

To create this chart I’ve used some example data (Excel file) that can be found on the zip file attached bellow. That file has several fields, enough to identify the tasks of a Project (such as ID, Start, End…).

In order to be able to pick the desired dates a master calendar has been used. Also, the IntervalMatch functionality has proved very useful by generating the dates between the start and end of the different tasks that the project has.

The Hierarchy functionality in the load script generates the different task levels.

There is something peculiar about the data I am using (Excel file). Colors for each task are specified in Hexadecimal format. So during the load script I converted them to RGB colors (color code used by QlikView).

After the data model has been completed a Pivot Table can be used to represent my Gantt chart. To make the chart stable, on the pivot table properties we need to uncheck the "allow pivoting" option. As dimensions we will have each task level, years months and days.

The expression is a bit different. First of all, I it's represented as an Image. That image is a png file (included in the zip file) with transparency that will let us see the background color of each cell.

The expression is as follows:

if(End=Start,'◆',
      if(date(DateID)=date(Today()),'gantt_part_today.png',
           if(Start = DateID and Group = 1,'gantt_part_end_l.png',
                if(End = DateID and Group = 1,'gantt_part_end_r.png',
                     if(End-DateID+1>0,'gantt_part_mid.png',
                          if(date(DateID)=date(Today()),'gantt_part_today.png'))))))

The background color for each cell is already stored in the data. So we just need to use it as a background color for our expression. In this case, if it is not a milestone, use the color value:

if(Mile =0, RGBColor)

That's all. The Pivot table now can be used as Gantt chart. I hope you find this example useful.

There are many documents and notes which talk about Set Analysis, but until the moment I started to write this manual I didn´t find any in Spanish, so I decided to write it.
Most of it is based on translated adaptations of previous works that I found in the community, to which I have added my own examples that I have been finding over the years. On the last page you will find the references.

This is a first version that will improve with your comments, corrections and contributions.
I hope you find it useful.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Son muchos los documentos y notas  que hablan de los análisis de conjuntos (Set Analysis), pero hasta el momento de comenzar a redactar este manual no encontré ninguno en Español, así que me decidí a escribirlo.

La mayor parte está basado en adaptaciones traducidas de trabajos previos que encontré en la community, a los que he añadido ejemplos de cosecha propia que me he ido encontrando a lo largo de los años. En la última página encontraréis las referencias.

Esta es una primera versión que iré mejorando con vuestros comentarios, correcciones y aportaciones.

Espero que os sea de utilidad.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Special thanks to Henric Cronstöm who encouraged me to write it when I was part of the spanish Qlik consulting team.