Categories
Common General

HR – Absence-Administration / Holidaydatabank

Holiday and illness-state administration

Administer absences of your employees with our beeye.hr solution. The newest Release 1.1 supports the following basic functionalities:

  • Working-time plan administration
  • Calculation of
    • New holiday claims
    • Monthly holiday factoring
    • Holiday restorings
  • Attendance administration
  • Special Absences
    • Wedding
    • Illnes state
    • Case of death
    • Relocation
  • Holiday-applications approving process
    • Applicant
    • Test and Permission
    • Correction
    • Booking
  • Employee Dashboard
  • Hint-System
    • Timeouts
    • Illness notifictaions
    • Substitution problems
Categories
Common

myStorage – SelfStorage Business Intelligence Platform on Caravel

A couple of days ago, airbnb – one of the shared economy flag ships – released it’s own BI platform to the open source community. Comparable to solutions like Kibana from Elastic – it’s much more BI than LOG driven. Those of you who know Logstash and Kibana know what I mean! 🙂 For our kind of business Caravel seems like a much better fit! It cares about security and you can build your own global KPI Dictionary to include the same values into several dashboards. Let’s get started!

Installation

  1. Install Docker http://www.docker.com
  2. Install the Docker Image from Caravel (pls. check http://hub.docker.com for latest version)

First steps

After you have started the docker container, pls. ensure that you have the python drivers installed for your database. We decided to run our mysql database from out storage software.

Setup your connection string

Screenshot 2016-04-06 21.48.35

Here you can add a new connection and pass the mysql uri like this:

mysql://soft2:XXXXXXXXXX@192.168.99.100:3306/soft2

Add tables to run your queries

Screenshot 2016-04-06 21.51.43

One of the problems you might face, that you need to know which tables are existing. If you aren’t sure about it, you can go back to the database connection uri and click the TEST Button and all tables in the database will be listed on the page.

Categories
Common

Eye-Tracking

What Is Eye-Tracking

Eye tracking is the process of using sensors to locate features of the eyes and estimate where someone is looking (point of gaze). Our technology relies on infrared illumination and uses advanced mathematical models to determine the point of gaze. We’ve spent a tremendous amount of effort on making it fully automatic and easy to set up so that it works accurately and reliably in a wide range of environments.Eye tracking can be used in a wide variety of applications typically categorized as active or passive. Active applications involve device control, for example aiming in games, eye activated login or hands-free typing. Passive applications include performance analysis of design, layout and advertising. Other examples are vehicle safety, medical diagnostics and academic research.

Our Service

We optimize your user experience. Back up your design with quantifiable data. We visualize how our UX-Design works using heat maps.
Data captured includes:

  • Thinkaloud voice recording
  • Webcam recording
  • Mouse clicks
  • Pupil diameter data
  • Where users stop scrolling
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
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
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
Common Developer Teaching QlikView /en

Lesson 4 – Structuring scripts and using functions

Overview

To not lose track in longer scripts, you have the possibility to structure the script. QlikView offers you to classify via index cards. Additionally we use easy functions to enrich our datamodel.

Generate an index sheet

To simplify the finding of ladelogics in the script, structure the script by creating new tabs or moving existing tabs. Try to find short but concise names for register riders to make navigation easier. It is especially useful to generate an “exit” register rider, which holds the command “EXIT SCRIPT;”. This makes it possible for complex data structures to stop “earlier” with the loading of the data and to analyze isolated logs.

Functions

Date functions

In addition to the company structure, which is almost always present in all projects, “time” is the most important main feature of any analysis.At this point, you will get to know the simplest date functions, but in later lessons we will learn how to build and integrate a “standard calendar”.

The most common date functions that can be applied to a field with date content are:

  • YEAR
  • MONTH
  • DAY
  • DAYOFWEEK
  • DAYOFYEAR
  • MONTHNAME

Everyone of these functions is powerful, e.g different economic years can be displayed, or even pointers to the last full month are possible. A well thought out time axis is going to help you create reports, charts and tables later on. You use these functions as follows:

//Loading of the calculation table

Bills:
LOAD CustomerId,
     ConractId,
     BillNumber,
     BillDate,
     YEAR(BillDate) AS Year,
     MONTH(BillDate) AS Month,
     DAY(BillDate)AS Day,
     Amount,
     Price;
SQL SELECT *
FROM Bills;

Functions and formulas for “calculations”

Apart from countless other options, QLikView also offers the possibility to calculate while loading data. This works as follows:

//Loading of the calculation table

Bills:
LOAD CustomerId,
     ...,
     Amount,
     Price,
     Amount * Price * (1-Discount) AS BillsLineSum;
SQL SELECT *
FROM Bills;

You obviously always have the possibility to calculate such calculations on the interface, but performance wise it is faster to calculate them while loading data – if possible.

Controlstructures during loading time

From time to time you have In some cases, you may need to apply certain rules depending on the field content. For this, it is possible to work with “IF-THEN-ELSE” functions.

// Loading of the calculation table

Bills:
LOAD CutomerId,
     ...,
     Amount,
     Price,
     Amount * Price * (1-Discount) AS BillsLineSum,
     If(wildmatch(Produkt,'*Tennisb*')>0,'Tennis','Other') AS Division;
SQL SELECT *
FROM Bills;

Here you can see how we compare the content of the field <Product> with the pattern “*Tennisb*” (* can be any amount of characters) and if the amount of hits is greater than 0, the division “Tennis” gets written, if not then “other”.

If you intend to refine search patterns you can set the number of charcters to be compared via “?” where each “?” represents a character.

Exercises

  • Extend your script from lesson 3 to a moving table
  • Build a “small” calendar with year, month and day
  • Extend your Pivot by a breakdown by time