topic Re: Calculate Average Dimensions in QlikView

Calculate Average Dimensions

Wed, 23 Apr 2014 15:25:45 GMT

I have a table as below

Date	Product	%low sales
01/01/01	abc	95
01/02/01	abc	97
01/03/01	abc	98
01/04/01	bcd	85
01/01/01	bcd	80
01/02/01	bcd	65
01/03/01	bcd	70

I want to create a chart that will take an avg of low sales and categories them as high performance and low performance based on avg % low sales and give me a product count

So, eg. if avg(%low sales) >95, low performance, high performance)

I cannot seem to get avg calculation working in the Dimension to create these categories to classify my product as low or high... Please help.

Re: Calculate Average Dimensions

TKendrick20 — Wed, 23 Apr 2014 15:38:49 GMT

In your Table you should have two Dimensions and two Expressions:

Dimensions (in this order):

Product

Date

Expressions (in this order):

AVG([%low sales])

if(AVG([%low sales]) > 95,'High Performance','Low Performance')

Hope this helps.

Re: Calculate Average Dimensions

Gysbert_Wassenaar — Wed, 23 Apr 2014 15:46:03 GMT

You can use this expression: if(avg(total <Product> [%low sales])>95,'low performance','high performance')

See attached example.

Re: Calculate Average Dimensions

d_pranskus — Wed, 23 Apr 2014 15:53:51 GMT

You need to specify the dimension to calculate averages. Please look at the first chart in example attached.

If you just want to categorize already existing %low sales, then look at the second chart. I used DUAL to have custom sort order for performance.

Hope this helps

Cheers

Darius

Re: Calculate Average Dimensions

Wed, 23 Apr 2014 16:18:22 GMT

Hi Tim,
This was working but I need it the other way round, I need a chart that shows, High and low as dimension and a count of products that fall under each category

Re: Calculate Average Dimensions

Wed, 23 Apr 2014 16:47:32 GMT

Hi Darius, this might work but not in the current form. you see I have a %low for all days in the past 30 days. for 1000's of products. I want to get an average for the month for each product and them classify the products as low performance or high.
In the table, I do not need date. When I remove the date from Dimensions the numbers are not right

Re: Calculate Average Dimensions

TKendrick20 — Wed, 23 Apr 2014 18:30:43 GMT

Hmm the closest thing I could get was to have:

Dimensions (in this order):

Product

Date

Expressions:

Label: High Performance: If(AVG(total <Product> [%low sales])>95,Count(DISTINCT Product))

Label: Low Performance: If(AVG(total <Product> [%low sales])<=95,Count(DISTINCT Product))

hope this helps point you in the right direction.

Re: Calculate Average Dimensions

d_pranskus — Wed, 23 Apr 2014 18:51:29 GMT

Then try to use the following expression for your dimension

=IF(AGGR(AVG([%low sales]), [Product]) > 95, 'High performance', 'Low performance')

Cheers

Darius