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
hic
Former Employee
Former Employee

Since I don't know what you have in vBillableCount, I cannot tell for sure. But I strongly suspect that the problem is in the Num() function. I suggest you try

   Num( ... , '0%')

and see if this works.

HIC

0 Likes
4,455 Views
Not applicable

Thanks Henric. If you don't mind (or anyone else viewing this post), i'd love your feedback of what I am doing wrong. The below straight table results is a small snippet of results -- all expressions working as expected. B2NB Ratio formula: =$(vBillableCount) / ($(vBillableCount) + sum([NonBillable]))

The above expression breaks down to vBillableCount = FTE Billable Hrs / 40 which yields FTE Billable Count. So for the ratio, I am simply taking FTE Billable Count / (FTE Billable Count + NonBillableCount) or

40.99 / (40.99 + 13) = 75.94. I then set the Total Mode of this expression to be Average of Rows.

But when  I drop this same expression into a text box, I am not getting the same result (in this specific data set, I get 85.41%). I tried appending TOTAL before the [NonBillable], but errored my expression. I am sure it is something minor I need to correct. Thanks Qlik braintrust!

       

Week EndFTE Billable HrsFTE Billable CountNonBillable CountB2NB Ratio
1,639.6040.991375.94%
11/20/15826.2520.66677.49%
11/13/15813.3520.33774.39%
0 Likes
4,455 Views
hic
Former Employee
Former Employee

The "Average of Rows" is a setting that only exists for Straight tables. It does not apply to text boxes. So I wouldn't use it...

About the formula... I assume that you have used aggregation functions like Sum() to define your columns, e.g.

* [FTE Billable Count] = Sum(Billable) / 40

* [NonBillable Count] = Sum(NonBillable) / 40

Then you can define your ratio as

Ratio = Sum(Billable) / (Sum(Billable) + Sum(NonBillable))

and if you want the total to show average over weeks, you should just change this to

Ratio = Sum(Billable) / (Sum(Billable) + Sum(NonBillable)) / Count(distinct [Week End])

This last formula you can use in a text box also.

HIC

0 Likes
4,375 Views
datanibbler
Champion
Champion

Hi Henric,

I think the thing with the average is quite logical, but nevertheless this is a very good explanation.

Maybe you can help me with a related problem I have - the underlying reason is probably easy, but I just cannot think of it:

- I have an Excel_database of shelves. Every shelf has a nr. of storage_lots (to keep things simple, I

   assume that every box is the same size, so I can put one box into one lot)

- From the company_database I get a nr. of boxes that are booked on any of the lots assigned to one

   shelf

=> From that results a usage_rate of the shelf_space. OK?

- Now, in that app I have two objects: A straight_table with the detail data (every single shelf)

   and a gauge just showing the overall usage_rate.

- One column in the table displays the usage_rate of every single shelf.

- For the top row I selected to display an average

=> For some time I had the problem that the overall usage_rate displayed by the gauge always differed

      from the figure in the top row of the table whereas every single shelf was displayed correctly and

      matched (between the two objects)

- To have a closer look at this issue, I finally reduced the nr. of shelves I use down to just a handful - still

   the figures are different:

    - When I take the overall count_of_boxes and divide it by the overall_nr_of_lots, I get one figure;

    - When I take the usage_rate of every single shelf and draw the average of those, I get another.

=> I am pretty sure that the latter method is just not possible, so I just take that figure out of the table and

     I have no problem anymore <-> but I would still like to know why.

Can you say a word to that?

Thanks a lot!

Best regards,

DataNibbler

0 Likes
4,375 Views
hic
Former Employee
Former Employee

The usage rate is a non-additive number, so you can not just average this. If all shelves had identical sizes, you could in theory average them. But as soon as there is one shelf with a different size, or you make a selection in some other property (thereby changing the "effective" size), the numbers need to be weighted. I.e. you can no longer just average them. 

No, just as you say, you need to (on all levels) take the overall count_of_boxes and divide it by the overall_nr_of_lots. See more on Additive and Non-Additive Numbers

HIC

4,375 Views
datanibbler
Champion
Champion

Thanks a lot! I will have a look at that. I still don't understand exactly why these figures cannot be averaged without weighting, but having confirmed from a pro like you that they cannot is something - it really takes a load off my chest. I was beginning to doubt my own capability because I just couldn't figure out why I had these differences ...

Best regards,

DataNibbler

0 Likes
4,375 Views
hic
Former Employee
Former Employee

An example: You have the following source data:

Assigned.png

Then you can calculate the usage:

Usage.png

However, this usage - the percentage - is a non-additive number. If you calculate the average of 50% and 100% you may (incorrectly) think that it is 75%. But look at source data, and you will easily see that it is not. Hence, you cannot use it for further calculations. You need to go back to the source to calculate a global average from the count of the [Assigned?] field..

HIC

0 Likes
4,375 Views
Not applicable

Hello Henric,

I’m new to Qlikview and SQL programming, I’m using the Qlikview personal edition and until now I have been able to obtain most of the data points I need, but I’m struggling while trying to calculate the average
between two different set of date fields (startdate & assigndate), and the formula seems to work but only when I select 1 record, but if I keep it open (all records – “thousands”) them the formula does not display anything.

The formula I’m using today is:

=if(Match(Team, 'A'), avg(Num( AssignedDate - StartDate, '#.#')),Null())

And I would like to be able to obtain the average number of days between assignment and stardate, and will them apply filters as needed for years, quarters, months, etc… for this calculation I'm using a textbox as KPI measurement.

Thanks for all the help you can provide me !!!!

Carlos

0 Likes
4,375 Views
hic
Former Employee
Former Employee

The problem here is that "Team" is a naked field reference - it is not wrapped in an aggregation function - it will return NULL if there are several values. See more on Use Aggregation Functions!.

The following should do the trick:

Avg({$<Team={'A'}>} AssignedDate - StartDate)

HIC

4,404 Views
Not applicable

Wow, THANKS Henric, the result is exactly what I needed - nice and simple !!! Thank you for your prompt support, Carlos

0 Likes
4,404 Views