Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Calculate Average Dimensions

I have a table as below

DateProduct%low sales
01/01/01abc95
01/02/01abc97
01/03/01abc98
01/04/01bcd85
01/01/01bcd80
01/02/01bcd65
01/03/01bcd70

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.

7 Replies
tkendrick20
Valued Contributor

Re: Calculate Average Dimensions

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

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

See attached example.


talk is cheap, supply exceeds demand
d_pranskus
Contributor III

Re: Calculate Average Dimensions

Hi

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

Not applicable

Re: Calculate Average Dimensions

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

Not applicable

Re: Calculate Average Dimensions

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

tkendrick20
Valued Contributor

Re: Calculate Average Dimensions

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.

d_pranskus
Contributor III

Re: Calculate Average Dimensions

Then try to use the following expression for your dimension

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

Cheers

Darius

Community Browser