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

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.

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:

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

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

Qlikview: Aggregation 1: How to calculate percentages at second dimension level

Sometimes, it may be necessary to display percentages for each item of dimension 1 in a way to add up to 100%, as in the following screenshot:

The solution is to use aggregation in the expression which calculates percentages:
expr 1: Total Sales: sum(SalesAmount)
expr 2: Percentage:  sum(SalesAmount)/aggr(NODISTINCT sum(SalesAmount),Country)

The applied solution can be viewed in the qlikview application which can be downloaded from the following link:
https://sites.google.com/site/quickdevtips/home/SecondDimPercentage.qvw

Qlikview: Calculated Dimensions 1: How to include a subset of a dimension in addition to dimension's itself

Once, I was asked to include 'European Union' total sales in a sales chart whose dimension was country field (i.e. only countries, there was no subset of countries like EU, BENELUX or NORDICS).

The solution included four elements:

• A calculated dimension which was composed of country field +  'European Union' 
• A variable to keep list of EU countries
• A variable to keep the list of selected countries in the country field
• An expression which could calculate total sales for country field and 'European Union' separately. 

The applied solution can be viewed in the qlikview application which can be downloaded from the following link:
https://sites.google.com/site/quickdevtips/home/CalcDim1.qvw