topic Re: Average functions in QlikView

Average functions

Anonymous — Thu, 13 Mar 2014 10:21:42 GMT

Hello, I've only just started working with QlikView this week as the company I work for is transitioning to it from Cognos and Excel.

We currently have a customer segmentation model built in Excel which I would like to replicate in QlikView.

Where I'm struggling is calculating average gross sales for our customer base as a whole.

I have a straight table that looks like this:

Location_ID	Gross Sales	Gross Margin	N. SKUs purchased
1	3	1.5	10
2	2	1	4
3	1	0.5	2
4	2	1	5

I'm struggling to fill the last two columns with the data I want for subsequent calculations. The last two columns should contain the same number all the way from the top to the bottom. Average SKUs in the example above would be 5.25 and Average gross sales would be 2.

What function should I be using and how should I be scripting it? Is it a variation on Aggr?

Thanks for your help,

Peter

Re: Average functions

Thu, 13 Mar 2014 10:23:32 GMT

Hi Peter, If you just use Avg(Total [Gross Sales]), that should work.

Re: Average functions

Thu, 13 Mar 2014 10:26:38 GMT

sum(Total GrossSales)/count(GrossSales)

Re: Average functions

Thu, 13 Mar 2014 10:28:36 GMT

Hi Peter,

For Average SKUs purchased, you can use : =AVG (TOTAL [N. SKUs purchased]) and for Average gross sales you can use : =AVG(TOTAL [Gross Sales])
Hope this helps!

Re: Average functions

jsingh71 — Thu, 13 Mar 2014 10:34:37 GMT

Hi,

I you want below output:

Then you need to add two expressions for calculating average.

1). Avg([N. SKUs purchased])

2). Avg([Gross Sales])

Re: Average functions

Anonymous — Thu, 13 Mar 2014 10:47:25 GMT

Thanks for your replies, I've managed to get the right answer for the the Average SKUs for which I used this:

=avg(Total Aggr(count(SKU), location_id)

Rather than have different values against each location I want to have the same value against each. I hope that makes some sort of sense?

Re: Average functions

Anonymous — Thu, 13 Mar 2014 10:52:55 GMT

Thanks for your replies, I've managed to get the right answer for the the Average SKUs for which I used this:

=avg(Total Aggr(count(SKU), location_id)

Rather than have different values against each location I want to have the same value against each. I hope that makes some sort of sense?