Sending Qlikview Reports in Excel

Would not it be nice if we could send some qlikview charts by email to a recipient automatically?

By using qlikview OCX and a piece of C# code, we can do this!!!

Let's examine the attached solution:

1) We need an excel template under template folder in which the position and the name of the qlikview chart to be transferred into excel is defined.
For example:
In the screenshot below, the definition CH01;image;0.5 in cell A1 means, export the chart CH01 from qlikview as an image and resize it by factor 0.5 and position its top-left corner with cell A1.
The definition CH02;text in cell H1 means, export the chart CH02 from qlikview as text and position its top-left cell with cell H1.

2) The output folder is used to copy the template into with a unique name and transfer the data from qlikview to this unique excel file before it is sent by email.

3) The qlikview file(s) should be placed in the same directory as template and output folder.

4) The excel template should have the same name as qlikview file. For example: if the qlikview file is named as Contoso-Simple.qvw then the excel file should be named as Contoso-Simple.xlsx.

5) The email account credentials which will be used to send the email are stored in app.config file.


How to use the application:

Before using the application, provide the email account credentials in app.config file which is under reportertest\Reporter\Reporter folder.

1) Manual use: double click on the reporter.exe under reportertest\Reporter\Reporter\bin\Debug folder. In the opening screen:

• select the folder where qlikview files and template and output folders are located
• select the qlikview file
• make any selection in the qlikview file shown in the big frame
• enter email address to which the excel report will be sent
• click on Send XLS

2) Automatic use:  The reporter.exe can also be called from console or scheduled to run automatically in windows scheduled tasks. While calling from console or scheduling it requires five arguments:

arg 1: full path of the file

arg 2: user name (if section access is used in qlikview file)

arg 3: user password (if section access is used in qlikview file)

arg 4: selections to be made in the qlikview file 

arg 5: the email address to which the report will be sent

For example: The following console command will open the reporter application, access the contoso-simple.qvw file, then make the selections (20071 and 20072 as CalendarQuarter & Online as ChannelName) and send out the excel report to xyz@gmail.com.

Reporter.exe "C:\\Users\\Bilge\\Desktop\\HappyStartinUSA\\OCX\\Contoso-Simple.qvw" "" "" "<CalendarQuarter:number:20071,20072><ChannelName:text:Online>" "xyz@gmail.com"

Source Code

Here is the source code and sample files (template, qvw file): reportertest.zip

Qlikview: Missing data points due to data reduction

If data reduction is enabled, the data available in the qlikview application is filtered according to the user logging in. For example, if the logging in user has no "sales" data associated with him/her for "Product A", "Product A" will be filtered out for this user.

This may make sense in most of the cases, however same thing exists for "date" data too. If the logging in user has no "sales" data for "July 2012", then once logged in, (s)he will not have "July 2012" data point in his/her "sales per month" chart. This may not be what is expected; expected chart behavior might be to have a "0" value data point for "July 2012".

A possible workaround for this issue is as follows:

1) Since data reduction is filtering out data, we need to prevent data reduction in "date" data. This can be achieved by the STAR function of qlikview (Pls see the script in the sample qlikview app). In the sample qlikview app, I preferred to create a duplicate of DateDim table in which data reduction is prevented, that way I can use the regular DateDim table as usual and the duplicate table (DateDim_ALL) for recuperating the filtered out "date" data.

In the sample qlikview application, please login as "User1" and observe that "CalendarMonth" field is missing "200802" and "200803" but  "CalendarMonth_ALL" field does not.

2) Create a chart with "CalendarMonth_ALL" as dimension and observe that missing dates are still missing :(.

3) Add a hidden expression (as first expression) that counts the "CalendarMonth_ALL" between the MAX and MIN dates available in fact table.

4) Set dimension limit to show "greater than zero", as dimension limit is applied to first expression, it is applied to the expression mentioned in the step above.

5) Make the chart read-only, as selections made on "CalendarMonth_ALL" won't be reflected across the application.

Please see the bottom chart in the sample application as an example. Try logging in with "User1", "User2" and "User3".

Here is the link to download the sample application: missing.qvw

Qlikview: Highlighting selected dimensions

If there are too many data points (or lines, or bars, etc) displayed in a chart, the user might want to highlight the ones which (s)he is interested in while still keeping the others in a lose color to be able to make comparison.

This can be done as follows:

1. Let user make selections in dimensions
2. Get selected values of each dimension into a separate variable when a button (e.g HIGHLIGHT) is clicked
3. If a dimension value of a chart do not exists in the values kept in the variable, then color it as light gray. This can be done by background color setting.

Here is a link to the example qlikview application: Highlight.qvw

Please, open the highlight.qvw, make a couple of selections in list boxes and click on Highlight Selected button and observe the highlighting.

Qlikview: How to send a report by email from access point

Step 1)
Create a report on a qlikview application which has some dummy data. (e.g. sales report in the attached qlikview file.)

Step 2)
Create a field in the qlikview applications to keep recipient email address. (e.g. recipient field in the attached qlikview file.)  and display it in a listbox.

Step 3)
Create a variable in the qlikview application to keep a string which will contain recipient's email address and the name of the output PDF file separated by semi colon.  (e.g vPdfName2 variable in the attached qlikview file.)

Step 4)
Create a listbox in the qlikview application which will display the content of the variable.

Step 5) 
Create a button in the qlikview application that will call the following macro:


Sub PrintRpt()
        'print the report and get the reponse back in a variable
set printResponse=ActiveDocument.PrintReportEx("RP01")

'get the name of the PDF file created by the print action above
sURL=mid(printResponse.URL,3)

        'get the email address of the recipient selected in the recipients listbox
set recipient=ActiveDocument.fields("Recipient").GetSelectedValues
set email=recipient.Item(0)

        'set the concatenated string (email address and name of the PDF file) as content of the listbox created in step 4
ActiveDocument.Variables("vPdfName2").SetContent chr(39) & email.text & ";" & sURL & chr(39), true

'set the path of the folder in which the text file will be crated
sFile="C:\_QVW\PDFfolder\WatchThis\email_" & replace(sURL,".pdf","") & ".txt"

        'Export the content of the listbox into a text file that contains the email address and name of the PDF file
set lb = ActiveDocument.GetSheetObject("LB05")
lb.ServerSideExportEx sFile , ";" , 1 '0=HTML, 1=Text, 2=Bitmap, 3=XML, 4=QVD, 5=BIFF
End Sub


Step 6)
Write a powershell script that can read a text file to get email address and path of the pdf file and send it by email.

Following posts have related examples:
how to loop through files in a folder by windows powershell
How to send email by windows powershell

Step 7) 
Schedule a windows task which will monitor the folder for incoming files and trigger the powershell script to read the file's content and send email with the PDF attached.

Following post have a related example:
How to monitor a folder and trigger an action for incoming files, in Windows 7
The only difference from the above example will be the source defined in the XML definition of the event filter. Instead of  "C:\windows\explorer.exe", we should now have "C:\Program Files\QlikView\Server\QVS.exe", because the text file will be created by qlikview server.

Test:
Deploy the qlikview application on the server and open it from access point and click on the button to see the result.

Example File: email_report.rar

How to loop through files in a folder by windows powershell

Step 1:
Create a powershell file named as loopfiles.ps1 (you can simply create a new text file and rename it as loopfiles.ps1).

Step 2:
Open the loopfiles.ps1 file and paste the following and save it:

############################


$fso = new-object -com scripting.filesystemobject
$folder = $fso.getfolder("C:\temp")

foreach ($f in $folder.files) {
    $f.path >> filesvisited.txt
$c = Get-Content $f.path
}

############################

Step 3:
Right click on the loopfiles.ps1 and select Run with Powershell.

At the end, one should have an output file (filesvisited.txt) with the names of the files in the current folder.

How to send email by windows powershell

The example below is built to run on gmail accounts.

Step 1:
Create a powershell file named as send_email.ps1 (you can simply create a new text file and rename it as send_email.ps1).

Step 2:
Open the send_email.ps1 file and paste the following and save it:


function fnSendEmail($Too,$Filee)
{
$smtpServer = "smtp.gmail.com"
$att = new-object Net.Mail.Attachment($Filee)
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer, 587)
$smtp.EnableSsl = $true
$smtp.Credentials = New-Object System.Net.NetworkCredential("your-email@gmail.com", "your-password");
$msg.From = "your-email@gmail.com"
$msg.To.Add($Too)
$msg.Subject = "powershell test"
$msg.Body = "This is a test email with an attachment"
$msg.Attachments.Add($att)
$smtp.Send($msg)
$att.Dispose()
}

Step 3:

• Start windows powershell 
• change the directory to where the send_email.ps1 file is located (pls see the line 1 in the screenshot below)
• call send_email.ps1 file (pls see the line 2 in the screenshot below), like that, one can use the fnSendEmail function in it
• call fnSendEmail function by passing to parameters (recepient's email address and the full path of the file to be attached) (pls see the line 3 in the screenshot below)

How to monitor a folder and trigger an action for incoming files, in Windows 7

Step 1:

Copy a file into a folder and look for the details of this event in windows event viewer. For example, if  copy a file named test - Copy.txt in C:\_QVW\PDFfolder\WatchThis folder, I found the following entry in the windows event viewer:

Make note of the event id (4656) and keyword (Audit Success) and that we are looking at security windows logs.


Step 2:
Go to the windows task scheduler and click on create task.

Step 2.1:
In general tab, name your trigger as My Folder Monitor as follows:

Step 2.2:

In triggers tab, click on New. In New Trigger window, set begin the task to On an event and select custom in settings, then click on New Event Filter button. In New Event Filter window, set the options as follows:

Note that we set the options according to what we have found in step 1.

Click OK and OK to finish this step.

Step 2.3:

In the action tabs, click on New. In the New Action window, set the options as follows:

Click OK and OK to finish this step.

Step 3:

Now, we need to tell the task which folder to monitor. 

Step 3.1:

Go back to the windows event that we looked at in step 1. Open the event details by double clicking the event and take note of the details of the event. We can use ObjectName and ProcessName details which are under the EventData node to fine tune our scheduled task.

Step 3.2:

Go to triggers tab of the scheduled task and click on Edit and then edit event filter. Note that, it is opened in XML tab and one cannot go back to Filter tab. From now on, the event filter can only be modified in XML. 

Modify the XML expression to restrict the scheduled task to trigger the action only for the files in C:\_QVW\PDFfolder\WatchThis folder, as follows:

Please note that &gt; means > (i.e greater than).

Now if you copy & paste or create a file in C:\_QVW\PDFfolder\WatchThis folder, a pop-up message should appear in the screen.

Hope it works on your side as well.

How to imitate two dimensional arrays in qlikview script

Below is a piece of qlikview script code which imitates two dimensional arrays:

2D array is supposed to be in the format like "MyArray" in the code below.
"GetItem" sub gets a item and assigns it to the given variable.
"FindItem" sub finds the item which corresponds to the given first dimension value and assigns it to the given variable.


Sub GetItem(fromArray,intoVar,X,Y)
Let firstDim=SubField(fromArray,',',X);
Let $(intoVar)=PurgeChar(SubField(firstDim,';',Y),Chr(39));
Set firstDim=NULL();
End Sub

Sub FindItem(fromArray,intoVar,SearchFor)
let k=fromArray;
For each t in $(fromArray)
Let s=t;
if SubField(t,';',1)=SearchFor then
Let $(intoVar)=SubField(t,';',2);
Exit for;
end if
next
End Sub


Set MyArray="'A;Turkey','C;Usa','D;Canada'";
Set vGetTest=Null();
Set vFindTest=Null();

Call GetItem(MyArray,'vGetTest',1,2); //This line sets 'vGetTest' to 'Turkey'
Call FindItem(MyArray,'vFindTest','C'); //This line sets 'vFindTest' to 'Usa'

Qlikview: Server: How to monitor a folder and trigger a QVS EDX task

I built a desktop windows application which monitors a certain folder and triggers an EDX task on qlikview server when a file is copied into the folder. The application also provides archiving option.

If you would like to try it yourself, please do the following steps:

1) Create an EDX task on the qlikview server:

2) Copy the EDX_Trigger_Desktop.exe and EDX_Trigger_Desktop.exe.config into the same folder, modify the values in the following entries of the config file according to your setup and run the exe file.
<add key="QMSAPIURL" value="http://localhost:4799/QMS/Service"/>
<add key="Taskname" value="EDXTEST"/>
<add key="TaskPassword" value="EDXTEST"/>
<add key="FolderToMonitor" value="C:\TEMP"/>
<add key="ArchiveFolder" value="C:\TEMP\ARCHIVE"/>

3) The application will display the values (folder to monitor, QMS URL, etc.) from configuration files. if necessary, these values can be modified manually in this screen:

Click the START button to start folder monitoring.

4) Copy a file into the folder which is being monitored and check if the EDX task is triggered on qlikview server.



The application and source files can be downloaded from the following links:

• Configuration and executable files:  release.zip
• Complete C# project:  EDX_Trigger_Desktop.zip

Facts, known issues, etc.:

• The application is developed with MS Visual Studio 2010 Express
• It triggers an EDX task on Qlikview Server version 11.

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:

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

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

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

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:

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

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