Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

If you want to display an average number of something in QlikView, you should use the Avg() function, right?

 

Wrong.

 

Yes, there is an Avg() function that returns the average value, but – this is usually not the value that you are looking for. The Avg() function returns the average transactional value, whereas you probably are looking for a larger amount.

 

For example, let’s say that you have an orders database where the grain - the most atomic level – of the data is Order Line. Each order can have several order lines and each order line has an amount. Then, the Avg() function will return the average order line amount, which is utterly uninteresting. No, you are most likely more interested in questions like “How much do we sell per month, on the average?”

 

In other words – a calculation of an average has an implicit internal grouping entity; the average per month, per day, per order, per delivery or something else. You can look at it as a two-step aggregation:

 

  1. Sum all the amounts – per each value of the internal grouping entity (e.g. month, day, order or delivery)
  2. Calculate the average of the sums from previous bullet.

 

In QlikView, you would calculate the average monthly sales value in one of the two following ways:

 

     Sum( Amount ) / Count( distinct MonthID )
     Avg( Aggr( Sum(Amount), MonthID ) )

 

… and similarly for orders, days or deliveries. Use of the Aggr() function will work, but it is not as fast as the first option, and should therefore be avoided.

 

Sometimes there are several internal grouping entities. You may for instance want to show the average monthly sales value per customer, i.e. you want to use both month and customer as grouping entity. Then you should use one of the following expressions:

 

     Sum( Amount ) / Count( distinct MonthID & '|' & CustomerID )
     Avg( Aggr( Sum(Amount), MonthID, CustomerID ) )

 

The Count() aggregation with a string concatenation will find every unique combination of month and customer.

 

Note that the internal grouping entity has nothing to do with the dimension you choose to use when you display it. It would make perfect sense to show the above number using Product as the only dimension, as shown in the graph below. Hence, the internal grouping entity is not necessarily visible in the end result.

 

Average graph.png

 

So, you need to figure out which internal grouping entity you want and then use this in a smart way in your expression.

 

And by the way – an internal grouping entity can be used also for other aggregation functions: Smallest, largest, most common: Min(), Max(), Mode(). But for these, you will need to use the Aggr() function.

 

HIC

32 Comments
Anonymous
Not applicable

Hello,

I'm trying to understand how the Aggr() function can be used to produce the same result across different straight tables when the same dimensions are not used.

For example, I want to know the average dollar amount of the sales orders entered by salesperson by month (the red dollar amount in table2 needs to match table1)

Straight table1 - shows the monthly averages for the selected salesperson and provides a total average over the rows

  • Dimension = Month
  • Expression = Avg(Aggr(Sum(Sales),Month,OrderNumber,Salesperson))

Straight table2 - provides a summary of the total averages by salesperson but the average does not match the straight table1's total average of the rows

  • Dimension = Salesperson
  • Expression = same calculation as straight table1 but what ends up happening is that the total sales for the entire period are divided by the total number of distinct orders.

table1.png

table2.png

Looking forward to your insight,

Teresa

0 Likes
4,600 Views
hic
Former Employee
Former Employee

I cannot understand why you have different numbers without looking into the app. It should work, as I see it. What happens if you instead use

   Sum(Sales) / Count( distinct OrderNumber )

which should result in the same number?

HIC

0 Likes
4,600 Views
Anonymous
Not applicable

Thank you for the reply.  The equation you suggested worked.

0 Likes
4,600 Views
PuriVelasco
Creator
Creator

@hic I am trying to use the Median for a moment but I am obtaining wrong values.

I use:

Time(Median([begin_date])) where begin_date is a field in date format.

What do I have to use to obtain a correct median in the same way you use the average?

Thank you very much.

 

0 Likes
3,364 Views
hic
Former Employee
Former Employee

@PuriVelasco 

Why this doesn't work, I cannot say. It should work. I would do the following

0 Likes
3,337 Views
PuriVelasco
Creator
Creator

@hic Thank you very much for your answer.

I have to use Time() because I need hours, minutes and seconds from date.

 

0 Likes
3,333 Views
david_chai
Contributor
Contributor

Hi Henric,

Need you advice on this:

 

0 Likes
1,349 Views
david_chai
Contributor
Contributor
  Reporting DateYTDYTD
RegionCurrencyCountryDaily Cash Avg.Yield
Total  14.782.37%
AsiaTotal 14.782.37%
AsiaIDRTotal5.083.38%
AsiaIDRIndonesia5.083.38%
USAUSDTotal9.700.16%
USAUSDUSA9.700.16%
0 Likes
1,345 Views
david_chai
Contributor
Contributor

The above Total Yield is wrong 2.37.

The value is 1.77 = 3.38 + 0.16 divide by 2

Do you know what is wrong?

0 Likes
1,336 Views
david_chai
Contributor
Contributor

I use:

Daily Cash Avg.:

SUM(AGGR(AVG([USD Eq Principal])/Factor,[Cash Value Date],Region, Currency, Country)) divide count(Month([Reporting Date]))

Yield:

((SUM([USD Eq Interest] divide DAYS) divide Factor) divide SUM([USD Eq Principal] divide Factor) *365)*1.25

 

0 Likes
1,313 Views