05 May 2012

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


04 May 2012

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