Categories
Common QlikView /en

Global Calendar

OHaving a well structured calender within your qlikview app allows you to build faster charts and reports. Speak to your internal customers about their report layouts and you’ll see that most people have a common sense on how to combine data over time.

Below you will find a list of fields which we recommend to add it to your default calendar.

Definition:

FieldnameFormulaDescription
Calendar.Datedate(Field)Date in the system format, e.g. 01.01.2016 (DD.MM.YYYY)
Calendar.Yearyear(Field)Date in the format 2016 (YYYY)
Calendar.LYearyear(Field-365)Previous Year in the format 2015 (YYYY)
Calendar.LLYearyear(Field-730)PrePrevious Year in the format 2014 (YYYY)
Calendar.YearTDif(Field1=in year to date full months only in the format (1=true, 0=false)
Calendar.YearCTDif(Field<=today(),1,0)1=in year to date in the format (1=true, 0=false)
Calendar.LYearTDif(Field1=in last year to date full months only in the format (1=true, 0=false)
Calendar.LYearCTDif(Field<=today()-365,1,0)1=in last year to date in the format (1=true, 0=false)
Calendar.LLYearTDif(Field1=in previous last year to date full months only in the format (1=true, 0=false)
Calendar.LLYearCTDif(Field<=today()-720,1,0)1=in previous last year to date in the format (1=true, 0=false)
Calendar.Qtrquartername(Field)Date in the Format 1.QTR 2016
Calendar.LQtrquartername(Field-365)Date in the Format 1.QTR 2015
Calendar.LLQtrquartername(Field-720)Date in the Format 1.QTR 2014
Calendar.QtrTDif(quarter(Field1=quarter to last full month
Calendar.LYQtrTDif(quarter(Field1=quarter to previous year last full month
Calendar.LLYQtrTDif(quarter(Field1=quarter to previous previous year last full month
Calendar.MonthLmonthname(Field)Date in the format May 2016 (ddd YYYY)
Categories
QlikView /en Tips n Tricks /en

extended dataloader

/**
 * LoadSourceTableByName
 * @param TableName String the name of the table that will be exported
 * @param Historical Boolean if a historical version will be saved on a monthtly basis, pls. check comment!
 * @version 1.01
 * @author Philipp Frenzel <philipp@frenzel.net>
 */

SUB LoadSourceTableByName(NameSpace, TableAlias, TableName, Historical)

LET varTableName = TableName;
LET varTableAlias = TableAlias;
LET varNameSpace = NameSpace;

QUALIFY "*";
 
$(varTableAlias):
LOAD *;
SQL SELECT * FROM spaceman.$(varTableName);
 
UNQUALIFY "*";
 
CALL Qvc.Log('No of rows loaded in table: $(varTableAlias)' & NoOfRows('$(varTableAlias)'),['INFO']);
 
// Here we store the table as is into the filesystem 
STORE $(varTableAlias) INTO "$(PATH_DATASTAGING)$(varNameSpace)_$(varTableAlias).qvd" (qvd);

IF Historical = 1 THEN

// ATTENTION, if you save historical data, pls. ensure that a folder with the tablename name exists within the datastaging root folder!
STORE $(varTableAlias) INTO "$(PATH_DATASTAGING)HISTORY/$(varTableName)/$(varTableName)$(VERSIONDATE).qvd" (qvd);

END IF

DROP TABLE $(varTableAlias);

END SUB

 

Categories
Common QlikView /en Script /en Tips n Tricks /en

QlikView Generating Year over Year Targets

From time to time you have to create budget figures upon a percentage change compared to the last year. This can be a little bit difficult, depending on the complexity of the KPI. The following example is build upon a pre-cleaned datamart and shows how you create the monthly figures for you budget.

/**
* Find the opening balance for the sqm this year
*/
OpeningSQM:
NOCONCATENATE LOAD Distinct  
     %SiteID, 
     date(monthend(ReportingDate) + 1) AS ReportingDate //need to cast it in the next month to have the start in january
     ,'BUD'  AS MM_TYPE
     ,'2015' AS BUD_VERSION
     ,'SQM_CUM_OPENING'  AS BUD_KPI
     ,sum(KPI.UnitMoveIn_SQM_CUM) - sum(KPI.UnitMoveOut_SQM_CUM) AS Budget.Value
FROM
[..\04 Datamart\EVENTS_ALL_MONTHLY_KPI.qvd](qvd) 
WHERE 
     month(ReportingDate) <= 12 AND year(ReportingDate) = 2014
GROUP BY
     %SiteID, 
     ReportingDate;

/**
* Let's add the SQM changes by month, to make the cumulated calculation
*/
Concatenate(OpeningSQM)
LOAD
      *
Resident FinalBudget
WHERE
	BUD_KPI = 'SQM';
	

//Lets build the cumulated sqm
FOR MonthIter = 1 TO 12

	Concatenate(FinalBudget)
	LOAD
		%SiteID
		,MM_TYPE
		,BUD_VERSION
		,'SQM_CUM' 					AS BUD_KPI
		,floor(max(ReportingDate)) 			AS ReportingDate
		,sum(Budget.Value)				AS Budget.Value
	RESIDENT OpeningSQM
	WHERE 
		month(ReportingDate) <= $(MonthIter)
	GROUP BY
		%SiteID
		,MM_TYPE
		,BUD_VERSION
	;	
	
NEXT;

DROP TABLE OpeningSQM;

As you can see the real magic here is done in the FOR-TO-Loop, here it shows a cumulated budget figure, but you can easily change to a mothly figure!

/**
* Find the opening balance for the occ units this year
*/
LET InsuranceFactor = 1.05;

CONCATENATE (FinalBudget)
LOAD 
	%SiteID,
	floor(monthname(ReportingDate) + 365) AS ReportingDate
	,'BUD'  AS MM_TYPE
	,'2015' AS BUD_VERSION
	,Lead.CleanLeadSource AS BUD_KPI
	,Lead.CleanLeadSource
	,count(DISTINCT %ActionID) * $(InsuranceFactor) AS Budget.Value
FROM
[..\04 Datamart\INVOICES.qvd]
(qvd)
WHERE
	year(ReportingDate) = 2014 AND 
GROUP BY
	%SiteID
	,monthname(ReportingDate);

 

Categories
Common QlikView /en Script /en

Load multiple GoogleAnalytics-Properties at once w/ dynamic metrics in QlikView

Our objective is to load GoogleAnalytics data, seperated in months with a dynamic metric, fixed dimensions and for multiple Properties. To achieve this we’re using the QlikView addon: QVSource. The script contains the following (important) variables:

  • gAppId = googleAnalytics PropertyId
  • vMetric = the dynamic metric, we want to load (note that this script will only load 1 metric per request)

First of all we need to define our googleAppIds within a static table.

GoogleId:
LOAD * INLINE [
    AppId, Land
    12345678, AT
    23456789, CH
    34567890, DE    
];

Now we have to loop through our table:

Let vStartTime = now();
Let vNr = NoOfRows('GoogleId')-1;

Trace $(vNr);

FOR i = 0 TO  $(vNr)
        Trace Loading $(i);

        LET vGAppId = peek('AppId',i,'GoogleId');
        LET vCountryId = peek('Land',i,'GoogleId');
        
        Trace start Sub loadGoogle $(vGAppId) and $(vCountryId);
        CALL loadGoogleId(vGAppId, vCountryId);
NEXT;

 

As our loadGoogleId Sub is still missing we have to add this here:

SUB loadGoogleId(gAppId, CountryCode)

        Trace loading the values day by day;
        Set x=1;        
        
        FOR zz = 2010 TO 2015
                FOR ii=1 TO 12 //monat
                                // Date period                        
                                LET jj = num($(ii),'00');
                                LET ee = num($(dd),'00');
                                LET ff = num($(dd),'00');
                                
                                LET vMonthend = left(monthend('01.' & $(jj) & '.' & $(zz)),2);
                                SET gDateStart='$(zz)-$(jj)-01';
                                SET gDateEnd='$(zz)-$(jj)-$(vMonthend)';
        
                                CALL LoadGoogle('users');
        
                                TRACE ende der schleife;
        
                                STORE GoogleAnalyticsConnectorV3_DataFromQueryURI INTO '$(PATH_DATASTAGING)\GOOGLEANALYTICS\$(CountryCode)_FILENAME_HERE_$(zz)_$(jj)_test.qvd' (qvd);
        
                                DROP TABLES GoogleAnalyticsConnectorV3_DataFromQueryURI;
                                x=x+1;
                        //NEXT; //next day
                NEXT; //next monat
        NEXT; //next jahr
END SUB;

Here is just a loop to address every single month within our prefered timespan. (one step for every month, as we want one table/file for every month). We call LoadGoogle in every step and store it afterwards.

LoadGoogle is the Sub, where the true magic happens. You have to build your LOADing-Statement with the QVSource-GoogleAnalyitcsConnector / DataFromQueryURI. Now you have to modify your string like the one below (just add the variables where they are required):

SUB LoadGoogle(vMetric)

GoogleAnalyticsConnectorV3_DataFromQueryURI:
LOAD
        dim_source,
        dim_adContent,
        dim_deviceCategory,
        dim_region,
        dim_hostname,
        dim_pagePath,
        dim_date,
        makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as date_qv,
        metric_users
FROM
[http://localhost:5555/QVSource/GoogleAnalyticsConnectorV3/?table=DataFromQueryURI&appID=&feed=https%3a%2f%2fwww.googleapis.com%2fanalytics%2fv3%2fdata%2fga%3fids%3dga%253A$(gAppId)%26start-date%3d$(gDateStart)%26end-date%3d$(gDateEnd)%26metrics%3dga%253A$(vMetric)%26dimensions%3dga%253Asource%252Cga%253AadContent%252Cga%253AdeviceCategory%252Cga%253Aregion%252Cga%253Ahostname%252Cga%253ApagePath%252Cga%253Adate]
(qvx);

END SUB

 

Categories
Common QlikView /en

Loading Google Analytics with QlikView

General

To keep reports compatible, the following dimensions are alway loaded as a minimum:

  • ga:browser
  • ga:deviceCategory
  • ga:pagePath
  • ga:date

Add Measures

Now as the dimensions are set, you can add up to 5 measures to be grouped by and displayed.

QlikView Load Script

Trace loading the values day by day;

FOR zz = 2012 TO 2015
 FOR ii=1 TO 12 //monat
  FOR dd=1 TO 31 //tag

// Date period
LET jj = num($(ii),'00');
LET ee = num($(dd),'00');
LET ff = num($(dd),'00');

SET gDateStart='$(zz)-$(jj)-$(ee)';
SET gDateEnd='$(zz)-$(jj)-$(ff)';

TRACE ab hier beginnt die Schleife;

GoogleAnalyticsConnectorV3_DataFromQueryURI:
LOAD 
	'DE' AS Land,
	*;
LOAD
	dim_browser AS Browser,
	dim_deviceCategory AS Device,
	dim_pagePath AS Path,
	dim_date,
	makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as date_qv AS ReportingDate,
	metric_users,
	metric_sessions,
	metric_pageviews,
	metric_exits,
	metric_exitRate
FROM
[http://localhost:5555/QVSource/GoogleAnalyticsConnectorV3/?table=DataFromQueryURI&appID=&feed=https%3a%2f%2fwww.googleapis.com%2fanalytics%2fv3%2fdata%2fga%3fids%3dga%2
%26start-date%3d$(gDateStart)
%26end-date%3d$(gDateEnd)
%26metrics%3dga%253Ausers%252Cga%253Asessions%252Cga%253Apageviews%252Cga%253Aexits%252Cga%253AexitRate%26dimensions%3dga%253Abrowser%252Cga%253AdeviceCategory%252Cga%253ApagePath%252Cga%253Adate]
(qvx);

TRACE ende der schleife;

STORE GoogleAnalyticsConnectorV3_DataFromQueryURI INTO '$(PATH_DATASTAGING)\GOOGLEANALYTICS\01DUMPFORMAT_USER_SESSION_PAGEVIEW_EXITS_EXITRATE$(zz)_$(jj)_$(ee).qvd' (qvd);

DROP TABLES GoogleAnalyticsConnectorV3_DataFromQueryURI;

  NEXT; //next day
 NEXT; //next monat
NEXT; //next jahr

If you don’t have QVSource running, you can use the query explorer and the url with the (1hour) token included to load the data like this. Then I recommend that you put this token into a variable to which will be passed to the script while you wanna load it.

Enhancement

If you have multiple sites you wanna load, just pack the hole load script into a sub and pass over at least the parameter:

  • gAppId

Then use this variable in your load url.

At least you can use an inline table to define your “websites” and then loop through all you sites:

GoogleId:
LOAD * INLINE [
    AppId, Land
    123, mydomain.CH
    456, mydomain.DE
    789, mydomain.AT
];

FOR I = 0 TO 2
	LET vGAppId = peek('AppId',I,'GoogleId');
	LET vCountryId = peek('Land',I,'GoogleId');

	CALL LoadGoogleId(vGAppId, vCountryId);
NEXT;

 

Categories
Developer Teaching QlikView /en Script /en

QlikView Script Sub for Dataloading

/**
 * LoadSourceTableByName
 * @param TableName String the name of the table that will be exported
 * @param Historical Boolean if a historical version will be saved on a monthtly basis, pls. check comment!
 * @version 1.0
 * @author Philipp Frenzel <philipp@frenzel.net>
 */

SUB LoadSourceTableByName(TableName, Historical)

LET varTableName = TableName;

QUALIFY "*";
 
$(varTableName):
LOAD *;
SQL SELECT * FROM spaceman.$(varTableName);
 
UNQUALIFY "*";
 
 
// Here we store the table as is into the filesystem 
STORE $(varTableName) INTO "$(PATH_DATASTAGING)$(varTableName).qvd" (qvd);

IF Historical = 1 THEN

// ATTENTION, if you save historical data, pls. ensure that a folder with the tablename name exists within the datastaging root folder!
STORE $(varTableName) INTO "$(PATH_DATASTAGING)HISTORY/$(varTableName)/$(varTableName)$(VERSIONDATE).qvd" (qvd);

END IF

DROP TABLE $(varTableName);

END SUB

Sorry, typo in the path variable, now fixed! Enjoy the script!

Categories
Common Developer Teaching QlikView /en

Project Documentation with github and markdown

A very important step while implementing more or less complex solutions is the availability of a documentation. While I’m really a fan of version based work, I will describe a quick an simple way, how I document my projects with github and markdown.

For all of you who don’t know markdown, it’s a simple “what you mean is what you get” syntax which can be compared to LATEX. (If you don’t know this eather, pls. give a search engine a visit) 😉

So what you acutally need is:

  • GitHub Account
  • TextEditor (min.) if you are the more “what you see is what you get” person, you’ll find a lot of editors in the web, even online based

 

Categories
Common QlikView /en

Prepare a new qlik project

Intro

If you prepare yourself for a “seeing is believing” workshop or going to start a new qlik project. Be aware that you should prepare your work environment!

System Requirements

Based upon the size of your data and the number of clients you need to “size” your server or developer workstation.

For a minimum developer workstation you should at least have:

  • 500GB HardDrive (2 Partitions 100,400)
  • >= i5 (min 2CPU)
  • 8 GB RAM

For a minimum server you should at least have:

  • 500GB HardDrive (2 Partitions 100,400)
  • >= 4 CPU (current tech)
  • 16 GB RAM

If you wonder why the server needs “more” ressources, it’s because each user will generate a little overhead working on the server!

Data Access

Depending on the customers data sources and to download little helpers, it’s helpful to have:

  • Access to the internet
  • File Access to all “text” based data sources
  • ODBC or OLEDB READ Account (we recommend to name it QVREADER)

Data Structure

People who work with the data and can deliver as much data structure knowledge as possible.

Data “Digging”

After you have access to the datasources, build an quick and “dirty” qvd datastaging layer. We normally suggest to do this in our qlik project template – so work is usable for production later. Take a look at the data loading “FIRST 10000” records from each datasourse and build a quick star schema.

Categories
QlikView /en QlikView GUI /en Tips n Tricks /en

QlikView – Color Blend by Dimension Value

Overview

Sometimes you wanna define a “color schema” by the values of a dimension. The easiest way is to generate a mapping table where each dimensionvalue gets an color assigned.

Downloads

First load the color table as CSV from here and save them into the same folder where you wanna create and run the QlikView Worksheet:

Sample

//Load ColorTable as IS
// ************************************************** //
// AUTHOR Philipp Frenzel <philipp@frenzel.net //
// ************************************************** //

SET varStringDocumentName = "=DocumentName()";

DimColorTable:
LOAD
 'col' & ColorName AS ColorName,
 RGB(Red,Green,Blue) AS ColorCode;
LOAD ColorName, 
 Red, 
 Green, 
 Blue
FROM
[Beeye QlikView Colortable.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);


// ************************************************** //
// AUTHOR Philipp Frenzel <philipp@frenzel.net //
// ************************************************** //

//first init variables
// ************************************************** //
// AUTHOR Philipp Frenzel <philipp@frenzel.net        //
// ************************************************** //

SET varStringDocumentName = "=DocumentName()";

ColorTable:
LOAD ColorName, 
     Red, 
     Green, 
     Blue
FROM
[Beeye QlikView Colortable.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);


// ************************************************************** //
// Dynamic Variables by Frenzel GmbH - COPY 2013 www.frenzel.net  //
// AUTHOR Philipp Frenzel <philipp@frenzel.net                    //
// ************************************************************** //

//Get the number of available rows in the rights table
LET varIntegerNoVariables = NoOfRows('ColorTable');

TRACE Number of Dynamic Color Vars: $(varIntegerNoVariables);
 
//Lets build the dynamic variables
FOR DatenbankIter = 0 TO $(varIntegerNoVariables) - 1
 
 	//here we build the logic for the color coding
	LET curVariableName = peek('ColorName',DatenbankIter,'ColorTable');
	LET col$(curVariableName)= 'rgb(' & peek('Red',DatenbankIter,'ColorTable') &',' & peek('Green',DatenbankIter,'ColorTable') &','& peek('Blue',DatenbankIter,'ColorTable') &')';
	
NEXT;

DROP TABLE ColorTable;

// ************************************************** //
// AUTHOR Philipp Frenzel <philipp@frenzel.net        //
// ************************************************** //


//then we assign the values to a dimension mapping
//create the dimension mapping
Map_Color:
MAPPING
LOAD * INLINE [
 YourFieldname, Color
 a, colCadetBlue
 b, colCadetBlue1
 c, colCadetBlue2
 d, colCadetBlue3
 e, colCadetBlue4
];

//load you data into the application

Testdimension:
LOAD * INLINE [
 YourFieldname
 a
 b
 c
 d
 e
];

//assign the colors
Dimension:
NoConcatenate LOAD
 *
 ,applymap('Map_Color',YourFieldname,'colBlue') AS ColorName
RESIDENT Testdimension;

DROP TABLE Testdimension;

If you wanna update the colors “online” just replace the first load with:

DimColorTable:
LOAD
 'col' & ColorName AS ColorName,
 RGB(Red,Green,Blue) AS ColorCode;
LOAD ColorName, 
     Red, 
     Green, 
     Blue
FROM
[http://www.beeye.org/wp-content/plugins/download-monitor/download.php?id=3]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq, no eof);

After this we create a bar chart with the YourFieldname as Dimension and 1 as Formula:

Screenshot 2015-04-02 12.55.30

To color it as we wanted to, we will edit the background formula for the formula as here:

Screenshot 2015-04-02 14.26.18Which leads us to the following result:

Screenshot 2015-04-02 14.27.15

Categories
Developer Teaching QlikView /en

Lesson 11 – complex data presentation

Overview

In this lesson we get to know more complex data diagrams, which are properly suited for “intergroup” analysis.

Trellis

Trellis graphs serve the same visualization with a uniform scale to visualize different categories.

Screenshot 2015-03-31 21.12.50

You can decide yourself which dimensions you want to “distinguish”.

Screenshot 2015-03-31 21.54.50

In the advanced settings you can define the quantity per column and line.

 Tables with conditioanl formatting

Keep in mind, these visualizations ONLY work with the graph type “Table”!

Screenshot 2015-03-31 21.13.02

This form of visualization has proven it’s worth “exclusively” for power-useers. Otherwise the information density would be too high to keep track.

 Sparklines

Also ONLY possible with graph type “table”. Exception: you can also integrate these so called “mini-diagrams” in a listbox.

Screenshot 2015-03-31 21.13.28

Tachometer

Screenshot 2015-03-31 21.13.41

Exercises

  • Build a simple Dashboard with 5 or less than 5 KPIs.
  • Look out for all the “mischiefs” and eliminate them.

We advise you to orient yourself with modern “CSS-Web-Frameworks”. For example – Microsoft Metro UI or Twitter Bootstrap.