As a result of our work always in search of the smarter solutions, we explain how to create a top and bottom in the same chart ...

This example is for a TOP & BOTTOM 5

First create your top and bottom ranking using a calculated dimension...

Choose Bar Chart / Style Orientation - Horizontal

=IF(Aggr(Rank(Sum($(vYourMeasure)),4),$(vDimension))<=5,$(vYourDimension)
,
IF(Aggr(Rank(-Sum($(vYourMeasure)),4),$(vDimension))<=5,$(vYourDimension),4),$(vYourDimension))<=5,$(vYourDimension)))

Your expression...

Sum($(vYourMeasure))

Different colors to differentiate between positive and negative...

=if(Sum($(vYourMeasure))>=0,RGB(0,146,69),RGB(255,0,0))

Sort...

Sort your calculated dimension by the use of the expression... Your expression:  Sum($(vYourMeasure)) ... Descending...

Feel free to modify further parameters like Presentation, Axe, etc...

At your disposal to answer questions and comments

André Gomes

The attached application shows you how to create a data-driven Ad-Hoc listing just using variables.  Using a data-driven approach you can also "copy" dimensions and expressions to an Ad-Hoc listing so the user can add additional columns.

An Ad-Hoc listing is a table where the user can choose what dimensions and expressions from the data model are displayed.

This is a very powerful means of examining the data.

Customarily in QlikView this has been done by creating a straight or pivot table and adding all possible expressions and dimensions.

Conditional expressions are added to each dimension and expression so that when the user selects a dimension or expression name from a list box, the associated columns are made visible in the table. This approach has a big drawback: the columns pop up wherever they were defined, not in the order selected.  Needless to say this looks very unprofessional.  The user shouldn't have to move columns around to fix their listing which was correct when they entered it.

It would be great if an Ad-Hoc listing could be created which preserves the order of selections.  To make this data-driven would be an additional plus.  This could be done with macros, dynamically creating a table using the API.  But macros have a number of drawbacks and essentially Qlik's position is that they should not be used.

The attached application shows you how to create a data-driven Ad-Hoc listing just using variables without any macros.

This approach preserves the order dimensions and expressions appear for a more polished listing.

The user can create an Ad-Hoc listing and then save the listing in a bookmark for later use.

In addition, you can attach icons to every graph and table in your application which will allow the user to apparently "copy" the dimensions and expressions used into an Ad-Hoc listing for detailed analysis.

I'll just provide a cookbook approach to just getting an Ad-Hoc listing working without going into how it's done.

If there's any interest I'll provide some explanation of how it works.

Download the sample application and associated spreadsheet and place them together in the same folder

Explore the application to see how the Ad-Hoc listing works and how a chart can "copy" dimensions and expressions to the listing

To create an Ad-Hoc listing for your own application, see the recipes in the attached document.

Beyond the basics of data modelling and the advanced topics of qlik datamodels there are more things which you might need if the amounts of data are increasing or if your datamodel is more complex. This meant if you have to combine multiple data-sources within more than one application and/or various applications are sharing a datamodel or it's even a cascade from (more or less nested) datamodels.

The view of things changed if you haven't only a few applications but an entire reporting environment with dozens or hundreds of applications and there are probably more then one people which work and maintain on it at the same time.

At first you should begin with a systematically approach. My suggestion is to start with a white paper and write down the requirements and how could be they solved, read books and postings like this, ask people which have done a similar work before and also consider to use external help for the difficult things - and be sure you have a plan how the end-result should look like. Nobody builds a tower without a plan from the architect.

But of course no plan is perfect and covered everything, you will need to adjust it several times and you will need by some aspects many attempts to gain your aims - but you shouldn't have a simple trial and error approach.

Therefore: Here a few links about the most importance things like: data-architecture (layer), folder-structure, naming standards, star-scheme vs. snowflake-scheme, various methods to match data (joins, mapping, concatenate, table-associations), security-topics and many more.

Best Practices for Data Modelling

Data Modeling issues and solutions

Best Practices in Data Modeling

Three Tier Architecture

QlikView Data Architectures

Beside those more theoretical deliberations exists an own group within the qlik community: QlikView Deployment Framework. to make such things easier and more practically.

Main topic here is the development of datamodels within qlikview but those are always imbedded in bigger enterprise structures therefore you should also think a little bit outside the box then you won't be successful with an isolated qlikview system.

qlik-view-project-methodology-handbook-v-10

Another important point is security and how you could manage Authentication and Authorization. The used authentication will be mostly defined on company level and could be often directly imbedded in qlikview with SSO. Authorizations instead are mostly applied on application level with section access (by use of the publisher loop and reduce is possible, too).

Beside those rather bigger things there are of course further topics on how qlikview worked under the hood which you should to know to improve the performance and the maintainability:

Symbol Tables and Bit-Stuffed Pointers

Symbol Tables and Bit-Stuffed Pointers - a deeper look behind the scenes

ByteSizes from Values in QlikView

The Importance Of Being Distinct

A Myth about the Number of Hops

Data Modelling: Clarity vs. Speed

Key field generation methods comparison

Another interesting point to the above links are synthetic keys - my personal recommendation is you should avoid synthetic keys but of course each coin has two sides and synthetic keys aren't a general problem. If you want to dive (really) deeper you should read this: Should We Stop Worrying and Love the Synthetic Key?  and this: Synthetic keys vs. AutoNumber() (- concatenation).

There is some content-overlapping within the above used links but I think the complement to each other is useful and of course you will find many more interesting postings here within the qlik community to these topic - the notes here are a good starting point to go further.

Have fun!

Marcus Sommer

Year, Quarter, Month and Week To Date are the common analysis that I seen many applications. I will share the expression to do here

First to do this your data model should have the DateField in number format by applying floor

Similar to this

Floor(DateField) AS DateNum //it will gives you one whole number to represent date

YTD - Year To Date

A date should be selected and it will look for the Starting date of the year to the selected date.

Ex: date selected is 21-03-2014 then YTD is 01-01-2014 to 21-03-2014

Expression would be

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

QTD- Quarter to Date

In the place of year use Quarter

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(QuarterStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

MTD- Month to Date

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

WTD- Week to Date

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(WeekStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

if you want you can set a variable value as 'Year', 'Month', 'Quarter', 'Week', lets say vToDate and go with single chart and single expression

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num($(=vToDate& 'Start(Max(DateNum))')))<=$(=Max(DateNum))"}>} Sales)

Will keep your expression simple

Regards,

Celambarasan

This is a companion post to the article KB 000004090

1) In order to function properly in Desktop as well as server environments, two separate ADMIN entities must be defined: the first one is associated with an empty string (or any string that does not match data in the reduction field), and this entity (typically a Publisher machine) is used for reloading the application data, but this entity cannot open the application in a server environment (as designed). The second ADMIN entity is associated with an "*" in the Section Access part of the script and is never allowed to reload data, but is on the other hand allowed to view all data in a non-reduced state on the server as well as desktop.

2) The initial table must NOT contain any record regarding the first ADMIN entity mentioned in (1) above, since this entity must be able to refer to a reduction value that is NOT a part of the logical data table outside the section access area. This entity must ALWAYS be defined in the section access area and nowhere else.

3)  The initial table must be dropped in order to avoid collisions between reduced and non-reduced data in the section application, otherwise, the strict exclusion flag will prevent any access to the server document. This is as designed, a bad reduction is always supposed to fail any attempt to log in on the document.

Assigning an ADMIN level to an entity that is subject to data reduction is NEVER a good practice and should be avoided, such an entity should either be elevated to full ADMIN level and consequently not subject to any data reduction or demoted to USER level (this is usually the most plausible action).

It is strongly recommended that the initial table is dropped, since this table is not fully reduced and does not contain all records that are part of the section access area (see #2 and #3 above) and, consequently, will not function as a proper link table to the logical data structure in section application (see modified example where a link table is defined based on the table in the section access area rather than the initial table).

4) It is possible to use Groups in section access (from an AD for example). They are added in the exact same way as users under NTNAME. When mixing users and groups in section access extra care needs to be taken with the permissions. If a user is present as both an individual user and a group member you should ensure they have the same permissions in both cases.

5) Do not have an Active Directory Group named as the service account running the QlikView Services. Any user member of this group will get the same privileges as the QlilView Service account (Usually Admin privileges)

6) Ensure the Strict Exclusion setting is checked in the Settings\Document Properties\Opening tab settings of the application if data reduction is being used. 

Please see the QVW attached (SA_EXAMPLE.QWV) for a script example. Section access user ID and credentials are set as following:

UID, PWD, Description:
A, A, Reload only - no server access.
ADMIN, ADMIN, No reload - full server access.
AAA, AAA, No reload - limited server access.
B, B, No reload - limited server access.
C, C, No reload - limited server access.
BFS, BFS, No reload - limited server access.
BHX, BHX, No reload - limited server access.
BRSGH, BRSGH, No reload - limited server access.

(Issues within and Overcoming through)

In General, we use section access for Data level, User – sheet or object level security on an application. Here, I came across a scenario which i should restrict multiple fields from different tables. That’s OK.

But during this data Reduction for a particular user, he/she may or may not have data in some Section access Restricted fields. As, we all know that SA will not grant access to users without data or Null () in any of the restricted fields i.e. when strict exclusion is checked under opening tab of document properties.

To overcome this and granting access to app for all users who have at least one data point in sync. We need to concatenate the SA table to each Master table based on the field mapping with each master. There by data will exist in all Masters and section access grants access to users & also this approach will impose nulls into other fields of masters. By this User may not get to see data which is not available, but can have access to all other required areas where data exists without subsiding into access denied initially.

Sample For Creating Above Scenario:

Temp_SA: //This table is being used for concatenating with other Masters and will be dropped at the end.
LOAD DEPT,ID_EMP
 INLINE [
    ACCESS, USERID, PASSWORD, DEPT, ID_EMP
    ADMIN, ADMIN, @123, *, *
    USER, A, 123, IT, 1
    USER, B, 123, SALES, 2
    USER, C, 123, NETWORK, 3
    USER, D, 123, CC, 4
];

Section Access;
LOAD * INLINE [
    ACCESS, USERID, PASSWORD, DEPARTMENT, EMPID
    ADMIN, ADMIN, @123, *, *
    USER, A, 123, IT, 1
    USER, B, 123, SALES, 2
    USER, C, 123, NETWORK, 3
    USER, D, 123, CC, 4
];
Section Application;


Dept_Master:
 LOAD * INLINE [
    DEPARTMENT, TurnOver
    IT, 22
    SALES, 33
    NETWORK, 455
];

Concatenate

Load DEPT as  DEPARTMENT
Resident Temp_SA where not exists (DEPARTMENT,DEPT); //For Checking only unavailable data field and concatenating with master.



Emp_Master:
LOAD * INLINE [
    EMPID, IssuesLogged
    2, 43433
    3, 3222
    4, 32243
];

Concatenate

Load ID_EMP as EMPID 
Resident Temp_SA where not exists (EMPID,ID_EMP); //For Checking only unavailable data field and concatenating with master.

DROP Table Temp_SA; // Dropping once Concatenation is done. 

Elaborating the above script and data set:

In Above sample data set with Section access mapped, the user “A” is not having any data in Emp_Master to get mapped. Similarly users “B” is not having any data in Dept_Master to get mapped and show case anything in APP.

So, due to strict exclusion checked. When we login with credentials of these both users, will get access denied to app.

For avoiding this we are concatenating the SA Table by calling it in resident with exists condition to all masters with required fields alone, here Where Not Exists condition is used to avoid creating nulls in already available fields by getting concatenated again.

There By using above Concatenation approach with SA, all users will have access to Application at any given point in time. If In case any data level error occurs or if data doesn’t have match required.

PFA QVW and Doc For Ref.

Thanks & Regards,

Hirish V

Hi,

I have seen many posts on community for showing negative values on charts like Block chart, pie chart etc. There is a solution available on community, but I thought to create the document so that everyone can utilize the same.

Lets say I have below data set

LOAD * INLINE [
Country, Score
US, 4971.765
UK, 4782.54
Japan, -200.941
India, 1412.181
Russia, 2445.292
];

Now I want to create the Block chart with above data set. So create the block chart with dimension as country and expression as sum(Score) , as soon as you create the chart you will get the error message that "positive and negative values in chart". But you can still display the negative values on chart with slight modification in expression.

Steps to create the block chart with negative value

1) Create the block chart

   Dimension : Country

   Expression : dual(sum(Score),fabs(sum(Score)))

If you want to display the distribution in % then use below expression

dual(num(sum(Score)/sum(TOTAL Score),'#0.00%'),fabs(sum(Score)))

Using dual function you can display the negative value with the base as positive value.

2) Go to chart properties -> Number Tab and check the Number format setting always as "Expression default". If the number format setting is not the expression default then negative values will be displayed as positive and also data will be displayed in wrong format. So if you want to provide any format like %, money you can define in the Dual expression itself using num function

Please see the attached application. In this application I have used the Fast change option so that you can see the results for multiple chart type as well

see also,

Control Chart

Benford's law

Bollinger Bands

Rolling N months

Regards,

Kushal

Hi all,

Qlikview Server has a number of licenses (AssignedCals), there may be a problem when these licenses come to the limit.

To avoid to click 200 times (delete foreach user, apply and then restore and applies again....) I create this simple program.

Launch the program (you need to be in the same network of Qlikview Server), enter the name of the QlikView Server and the number of the port (you can see these info in the address of the "QlikView Management Console"), once you have entered this information, the program connects to the Management Console and allows with few clicks to "clean" all licenses .

Work with latest Qlikview Server version (12.x).

[UPDATE 02-2017]

The latest version do all automatically (cancel and restore), and finally the program closes.

So once you open the program, just wait.

Bye,

emakio

Following a number of requests from Partners and New Joiners last year I've created a number of different QlikView templates which can get you started quickly. The focus has been on setting the framework to allow you to create applications quickly and easily that look modern and professional. Don't forget our QlikView Developers Toolkit which has a wealth of backgrounds, buttons and icons to assist you.

Enjoy.

Last week I got a question to get number of working days between two dates

1. Excluding Public Holidays
2. Including Saturday as Half Working Day
3. Including Saturday as Full Working Day.

Have done some work and created enclosed QlikView File.

Suggestion and Correction would be highly appreciated !

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.

This document shows the Difference between "Sum of Rows" and "Expression Total". Also how this can be implemented in Straight Table and Pivot Table in QlikView.

Hope this would be helpful for beginners.

Thanks,

Nisha

Power Tools for QlikView

Version: 1.3.2

..again! A small update of one tool, and test of another.

Download here

https://www.dropbox.com/sh/8hac72aljgqlr7d/AAASkzbM2H1OoVcLE_wa0kWGa?dl=0

For the absolute latest version, please see Power Tools for QlikView - One-stop-shop

QlikView 12.X support matrix

Reviewed/updated with support for QlikView 12

• QlikView Server Agent *tested* - 1.6.2 - supported
• QlikView Server Super Agent *updated*- 1.7.3 - supported

Release notes here: Power Tools 1.3.2 for QlikView - Release Notes

Readme here: Power Tools 1.3.2 for QlikView - Readme

Want to collaborate?

Do you have questions, thoughts, suggestions or bugs to report on Power Tools? Please post in the forums on QlikCommunity: https://community.qlik.com/community/qlikview/management

Also check out our (currently dead, soon to be revived) Twitter: @QvPowerTools

DISCLAIMER

Power Tools are a collection of software programs and tools used for troubleshooting purposes only. Power Tools are provided free of charge and are not supported. Power Tools are not official QlikView products and are provided without warranty. Use of Power Tools is entirely at the user's own risk.

Hi Qlikers,

Firstly, kudos to avinashelite‌ for giving us the below method to load the all excel files with the multiple sheets.

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

Using above method we can load the multiple excel files with multiple sheets even though all the sheets have different names.

Above method  works only in below scenarios

1)  When all the files having the same number of sheets.

2)  When all the sheets of excel files having the same number of Columns with same name.

But below method works even though you don't have same number of columns and sheets in excel and having blank sheet in excel.

// Define the Path

LET vExcelFilePath = 'D:\Test';
LET vQVDFilePath='D:\Test';

SUB CreateQVDFromAllExcelFiles(vPath)

FOR EACH vFileExtension IN 'xlsx'

FOR EACH vFile IN FILELIST(vPath & '\*.' & vFileExtension);

ODBC CONNECT TO [Excel Files;DBQ=$(vFile)];

Temp:
LOAD *;
SQLtables;
DISCONNECT;

Data:
LOAD * INLINE [
junk ];

FOR i = 0 TO NOOFROWS('Temp')-1

LET vSheetName = PURGECHAR(PURGECHAR(PEEK('TABLE_NAME', i, 'Temp'), CHR(39)), CHR(36));

CONCATENATE(Data)
LOAD  *,
FILEBASENAME() AS FileName,
'$(vSheetName)' AS Sheet_name
FROM $(vFile)(ooxml, embedded labels, table is [$(vSheetName)]);

NEXT i

DROP TABLE Temp;
DROP FIELD junk;

NEXT vFile
NEXT vFileExtension

Set ErrorMode=0;
Drop Field A;     // When there is blank sheet in excel file, field A is created
Set ErrorMode=1;

STORE Data into $(vQVDFilePath)\FullData.qvd;
DROP Table Data;

END SUB

CALL CreateQVDFromAllExcelFiles('$(vExcelFilePath)');

LET i = Null();

Feel free to provide your suggestions

Thanks & Regards,

Kushal Chawda

Greetings,

For the longest time, I have been trying to improve the quality of an exported image out of Qlikview.  Sometimes my end users want to just copy an image to the clipboard and paste it into an e-mail or a Word document.  By default these images export with less than desirable quality.  In particular, I use Nprinting and the image quality was so poor, we almost could not use the application.

Well, sometime a blind squirrel finds a nut or in my case the lottery ticket that wins you a millions dollars.

There is a setting in the User Preferences in Qlikview called Clipboard Zoom.  See attached Word Document.  By default, this setting is at 100 % in order to minimize the file size of the output.  This also leads to less than desirable quality.  I included an image from the sample surgical dashboard at 100 %.  I then change the setting to 400 % and copied the image to the clipboard and pasted the same image to the Word document   The results are astounding.  If you view the 400 % file, you can enlarge it and still get good quality.  The only sacrifice is that the file size of the image is substantially bigger.  I will gladly trade file size for image quality.

Try zooming on the images in the Word document.  It is night and day difference.

Now, here is the tricky part.  The setting is a User Preference.   So, even if I change it on my developers machine, I need to make sure the setting is changed on my end users desktop client.   If you are using Nprinting, you can specify the user name and password when you are connecting to the source data.

As a general rule, you should probably size your objects in Qlikview about the same size that you would like to appear in your external document after you export it.  This will minimize possible distortion as you scale it further.

I hope this post is helpful for you today.

Mike Czerwonky

BI Developer

Once loading data into the QlikView script/application, data validation becomes one of the first tasks to accomplish. Here, I have provided a simple was in order to build a data validation sheet in order to analyze data by selecting table and field. The ability to obtain statistics for the field and values, make it easier in order to perform simple data quality checks.

You can download this script and copy the QlikView Objects within your own script/application:

Dear Community,

I am sharing this document as I was always searching for list of all Qlik Chart functions in order for learning purpose. I couldn't find so I have created a list of all the chart functions available in Qlik. All the functions are available in Qlik but just in case if you want an ordered list.

I hope this will be helpful. 

Best Regards,

Arpit Vijay

https://www.linkedin.com/in/arpitv/

Dear Community,

Please find a new document explaining the LOAD command.

This document was first the content of personal notes. It has become bigger and bigger: I just hope it is not too big. it contains 60 pages: let's say that there is almost a tutorial even if there is no exercice.

Basic syntax is first explained but less basic syntax is also reviewed.

Please enjoy ...

Fabrice

PS. compared to version 1, I took into account HIC' comment and added two sections: INPUTFIELD (to enter some data into QlikView) and Generate Random Data (for test purposes)