30 June 2012

Qlikview: Security 3: Data Reduction 3

Scenario:

We have a sales team in which every salesman is assigned to a market (e.g. USA, France, etc...) and mainly make sales in their market but are also allowed to make sales out of market. For example a USA salesman mostly makes sales in USA but sometimes makes sales in European countries too.

In such a case, the USA manager should be able to see all the operations of his team, including their out of market sales in addition to USA sales.

Solution:


In this case, we can create a intermediate table which keeps salesmen's main market, and we can do the data reduction over this table's market field. By this way, we will be able to do the data reduction of main sales table by Salesman field rather than Market field.

When we filter/reduce the data in the intermediate table (the one on the left) by market, it will filter/reduce the data in the main sales table by salesman:




Here is the link for implemented solution:

Please log in with the following userid, password pairs and observe how data is filtered/reduced. When you login as ADM, you can see all data and also the script.

userid     password
---------  -----------
ADM        ADM
USmanager  USmanager
FRmanager  FRmanager





29 June 2012

Qlikview: Security 2: Data Reduction 2



If we need to limit data according to one's position in employee hierarchy, let's say if everybody can only see the data of the people who are reporting to himself/herself, then we can use the hierarchical table created by HierarchyBelongsTo, to limit the data access:

1) If it does not exist, create the adjacent nodes table by HierarchyBelongsTo statement. This table will have a AncestorName (can be named differently) field which keeps the ancestors of each employee.

2) Include AncestorName field in Section Access table, and enter the person's name who will only be able to access the data of the people who are reporting to himself/herself:

ACCESS USERID PASSWORD COUNTRY STATE REGION CONTINENT ANCESTORNAME
------ ------ -------- ------- ----- ------ --------- ------------
USER   DAVIDP DAVIDP   ALL     ALL   ALL    ALL       DAVID PROBST


For demonstration purposes, I have also created expanded nodes table by Hierarchy statement, this table lets me to display the employee hierarchy in a treeview listbox. Please download the following application and login as  ADMIN (password is also ADMIN) or DAVIDP (password is also DAVIDP) and see how data is reduced. When you login as ADMIN you can see all the data and also the script.

https://sites.google.com/site/quickdevtips/home/security2.qvw?attredirects=0&d=1






Qlikview: Security 1: Data Reduction 1

Sometimes, we may need to filter our data according to logged in user's geographical profile. If we have a well structured geographical data associated with our fact data, then the solution is easy for the following scenarios. Please note that I assumed, we have fields named as COUNTRY, STATE, REGION and CONTINENT in our data.

1) If the user Jean-Paul is only allowed to see France data, then we can add the following row into our Section Access table and reduce the data to France only for Jean-Paul:

ACCESS  USERID  PASSWORD  COUNTRY  STATE  REGION  CONTINENT
------  ------  --------  -------  -----  ------  ---------
USER    JPAUL   JPAUL     FRANCE   ALL    ALL     ALL


2) If the user Brian is allowed to see United Kingdom and Ireland data, then we can add the following rows into our Section Access table and reduce the data to United Kingdom and Ireland only for Brian:

ACCESS  USERID  PASSWORD  COUNTRY  STATE  REGION  CONTINENT
------  ------  --------  -------  -----  ------  ---------
USER    BRIAN   BRIAN     UK       ALL    ALL     ALL
USER    BRIAN   BRIAN     IRELAND  ALL    ALL     ALL


3) If the user EuropeManager is allowed to see data of all European Countries, then we only need to add the following row into our Section Access table:


ACCESS  USERID   PASSWORD  COUNTRY  STATE  REGION  CONTINENT
------  -------  --------  -------  -----  ------  ---------
USER    EUR_MAN  EUR_MAN   ALL      ALL    ALL     EUROPE


4) If the user George is only allowed to see Florida (USA) data, we need to do the data reduction with two fields in Section Access table, as follows:



ACCESS  USERID   PASSWORD  COUNTRY        STATE    REGION  CONTINENT
------  -------  --------  -------------  -------  ------  ---------
USER    US_FL    US_FL     UNITED STATES  FLORIDA  ALL     ALL




5) If we need to have a regional filter for a user and the region is not defined in our data, then we can define the region in a separate table in Section Application. So, we have two steps to fullfill this requirement:



5.1) Define region by creating a new table (let's name it as REGIONS) in Section Application:


REGION         COUNTRY
--------------  -------------
WESTERN_EUROPE AUSTRIA
WESTERN_EUROPE BELGIUM
WESTERN_EUROPE FRANCE
WESTERN_EUROPE GERMANY
WESTERN_EUROPE LIECHTENSTEIN
WESTERN_EUROPE LUXEMBOURG
WESTERN_EUROPE MONACO
WESTERN_EUROPE NETHERLANDS
WESTERN_EUROPE SWITZERLAND

5.2) Add the following row to the into our Section Access table:

ACCESS  USERID  PASSWORD  COUNTRY  STATE  REGION          CONTINENT
------  ------  --------  -------  -----  --------------  ---------
USER    WEUR    WEUR      ALL      ALL    WESTERN_EUROPE  ALL



Following is the link for the implemented solution, please login with the userid and password pairs mentioned above and observe how data is reduced. When you login with userid ADMIN (password is also ADMIN), you can see the all data and also access the script.

https://sites.google.com/site/quickdevtips/home/security1.qvw?attredirects=0&d=1


20 June 2012

Qlikview: Calculated Dimensions 2: Cumulative Sum


If we have a gap in our data, then Full Accumulation mode (in expressions tab of chart properties) may not work as we like.

For example, if we have sales data for a country over 12 months and if there is no sales data for August, we will have a chart which is missing August, as follows:

And we will also miss the data for the country (in this example it is Turkey) in a Full Accumulation mode, please see the August bar in the following chart:

Normally, since there is no data for August, we would expect to see the same value in August as July, in a cumulative sum chart.




To resolve this, we can do the followings:

  1. Use calculated dimensions for 12 moths. Such a calculated dimension ensures that we have 12 months displayed in the chart
    vMonths='Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'

    calculated dimension =valuelist($(=vMonths))
  2. Deactivate suppress zero values option in the presentation tab of chart properties
  3. Activate Full Accumulation mode in the expressions tab
Please see the August bar in the resulting chart below, now it display data for Turkey in August bar:


Here is the qlikview file:


13 June 2012

Qlikview: How to find a dimension value which corresponds to MAX or MIN of an expression


Scenario:
We need to find the countries who have the MAX and MIN sales and store them in variables to use in  set analysis.




Solution:
FIRSTSORTEDVALUE function can sort the fields according to a sort-weight (another field or an aggregation) and return the corresponding value of the field.


In our case we need to sort the countries by their total sales, thus we will use AGGR(sum(sales),country) as our sort-weight in FIRSTSORTEDVALUE function:

vCountryWithMaxSales =FirstSortedValue(Country, -Aggr(sum(Sales),Country))


The above expression returns the country which has the MAX sales. 


By default, the FIRSTSORTEDVALUE function sorts in ascending order. For sorting in descending order, we use the minus sign in front of the sort-weight. 


So, if we would like to get the country which has the MIN sales, we can just remove the minus sign:

vCountryWithMinSales =FirstSortedValue(Country, Aggr(sum(Sales),Country))


Warning:
If more than one country have the same MAX or MIN sales, then the FIRSTSORTEDVALUE function will return NULL. To avoid this, we can use DISTINCT qualifier:

vCountryWithMaxSales=FirstSortedValue(Distinct Country,-Aggr(sum(Sales),Country))
vCountryWithMinSales=FirstSortedValue(Distinct Country,-Aggr(sum(Sales),Country))



Sample:
https://sites.google.com/site/quickdevtips/home/findmax.qvw?attredirects=0&d=1





11 June 2012

Qlikview: Macro: How to export multiple objects into excel


By the help of macros, we can export data from multiple objects (table charts, table boxes) into excel.

All we need to do is to pass the Object IDs in the highlighted line of the macro code below and run the macro by a Button.

Pls. note that Current Local Security level (in the edit module screen) should be set to Allow Sytem Access level.

Macro code:

sub launchXL
set oXL=CreateObject("Excel.Application") 
oXL.visible=True
oXL.Workbooks.Add
aSheetObj=Array("TB01","CH01")
for i=0 to UBound(aSheetObj)
oXL.Sheets.Add
Set oSH = oXL.ActiveSheet
 oSH.Range("A1").Select    
 Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))
obj.CopyTableToClipboard True
oSH.Paste 
sCaption=obj.GetCaption.Name.v
set obj=Nothing
oSH.Rows("1:1").Select
oXL.Selection.Font.Bold = True
oSH.Cells.Select
oXL.Selection.Columns.AutoFit
oSH.Range("A1").Select    
oSH.Name=left(sCaption,30)
set oSH=Nothing
next
set oXL=Nothing
end sub


Here is a sample qlikview application: https://sites.google.com/site/quickdevtips/home/excelmacro.qvw?attredirects=0&d=1




Qlikview: How to refresh/reload data automatically in qlikview desktop

If you do not have qlikview server, you can refresh/reload your qlikview application by windows scheduled tasks.

Let's say you have a qlikview application named as ReloadME.qvw and it should be reloaded automatically everyday at 02:00 am.

Here is how you can do this:

Assumptions:
1) ReloadME.QVW is under C:\Temp folder
2) Qlikview's executable file (i.e. QV.EXE) is under C:\Program Files\QlikView folder


Steps:
1) Create a bat file with the following lines and name it ReloadMe.bat
       cd C:\Program Files\QlikView
       qv.exe /r C:\Temp\test.qvw
A sample file is in the following zipped file: https://sites.google.com/site/quickdevtips/home/ReloadMe.rar?attredirects=0&d=1
Hint: A bat file can be simply created by renaming a txt file (e.g. test.txt) as a bat file (e.g. test.bat) and can be edited by notepad.


2) Create a windows scheduled task (In Windows 7, its location is Control Panel --> All Control Panel Items --> Administrative Tools --> Task Scheduler) which will run ReloadMe.bat everyday at 02:00 am.



03 June 2012

Qlikview: Aggregation 3: number of MAXIMUMs in a dimension field

Scenario:
We have a data-set containing the market price of some commodities. And we need to find:
  1. The months in which the maximum market price of each commodity is reached
  2. How many commodities have reached their maximum market price in each month? (e.g. if only commodity A and commodity C have reached their maximum market price in April, then it is 2 for April)

Solution:
  1. Finding the maximum price for each commodity is easy, we will just create a straight table with COMMODITY field as dimension and MAX (PRICE) as expression. However, our aim is to find the months in which the maximum market price of each commodity is reached. So we will add a second expression as below:
             =MaxString(if(Price=aggr(NODISTINCT Max(Price), Commodity),Mth))
       
  2. The solution of item 1 will lead us to a solution for item 2. We just need the count of the COMMODITYs which are associated with the MONTHs returned by the second expression used in the solution of item 1.  So, if we use the AGGR function as follows, we can count the COMMODITYs:

    dimension:  =aggr(MaxString(if(Price=aggr(NODISTINCT Max(Price), Commodity),Mth)),Commodity)

    expression:  =count (Distinct Commodity)

Here is the implemented solution:




Qlikview: Aggregation 2: Dynamic Aggregation in Pivot Table

Scenario:
We have a sales data about how many items each sales person sold in each month.
And we need a pivot table which:

  • will show the number of sales person who sold more than two items in a given month.
  • will show the number of sales person who sold more than five items in a given quarter (i.e. when the pivot table is collapsed to quarter level)
  • will show the number of sales person who sold more than ten items in a given year (i.e. when the pivot table is collapsed to year level).

Solution:

  • We will make the calculation with AGGR function but we need 3 different formulas (one for each different level, i.e. month, quarter, year). I prefer to keep the formulas in variables:
    • vMthLevel = sum(if(aggr(sum(Quantity), Yr,Qtr,Mth,SalesPerson)>2,1,0))
    • vQtrLevel = sum(if(aggr(sum(Quantity), Yr,Qtr,SalesPerson)>5,1,0))
    • vYrLevel = sum(if(aggr(sum(Quantity), Yr,SalesPerson)>10,1,0))
  • To find out at which level we are in the pivot table, we will use DIMENSIONALITY function
  • And finally, in our expression, we will use PICK function to pick one of the three formulas according to DIMENSIONALITY.    
    • =pick(Dimensionality(),$(=vYrLevel),$(=vQtrLevel),$(=vMthLevel))

Below is the link for the implemented solution, pls. check the pivot table and see if it provides expected behavior:
https://sites.google.com/site/quickdevtips/home/aggregation2.qvw?attredirects=0&d=1