# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for
Did you mean:
Not applicable

## Calculated dimension - Need help to understand one line script

This is the line which creates a calculated dimension for a straight table -

Aggr(Count(DISTINCT [From - To State Code]), [Carrier Name])

Carrier Name holds the names of commercial airline companies. From-To state refers to states in the USA.

How does qlikview process this line of script ? Can someone show this graphically ?

Thanks.

4 Replies
MVP

graphically ?

make a chart with

dimension     Carrier Name

expression     Count(DISTINCT [From - To State Code])

QlikView Technical Brief - AGGR.docx

Partner

Hi John,

This line just count of unique  `From-To` for every  Carrier.

If you set it in dimension you just get a numbers and corresponding them Expressions.

For Example you have  a table:

 carrier from-to no sales de A 3 140 ee B 2 102 ee D 6 300 qe A 1 100 qe B 4 20 qe C 5 120

Result table will be:

 =aggr(count( distinct [from-to]),carrier) =sum(sales) 782 3 240 2 402 1 140

Means that Carrier 'qe' has 3 distinct from-to codes, `ee` has 2, and `de` has 1.

Graphically Carrier will be Dimension, count(distinct ...) will be Expression

Hope it helps.

Andrei

Not applicable
Author

That doc uses long examples. I found a small and much better one for just AGGR(). Easy read. I am not sure if the example is 100% correct -

Aggr() function

I am trying to compare this to SQL (in SQL server). I did it like this (bus = carrier/flight, route = from-to) -

 bus route a 1 a 2 b 1 b 3 c 1 c 4 c 6 c 7 b 11 d 1 d 2

-- Get the number of routes (call it RouteCount)

-- for each bus. Then, find the number of

-- unique RouteCounts. Find how many buses

-- serve each of these RouteCounts

select Routes, COUNT(Bus) as NumOfBuses

from

(

select bus, COUNT(route) as Routes

from rides

group by bus

) as Q1

group by Routes

The result of the above query is equivalent to AGGR()

 Routes NumOfBuses 2 2 3 1 4 1
MVP

I think in your example the Qlik aggr  "aggr(COUNT(route), bus)"  is  the inner query

select bus, COUNT(route) as Routes

from rides

group by bus

bus  Routes

a      2

b      3

c      4

d      2

then you use the aggr as a calculated dimension

dim          aggr(COUNT(route), bus)

expr          count(distinct bus)

source data at the left

aggr top r

result bottom r