topic Re: Counting average on specific sum in QlikView

Counting average on specific sum

Thu, 06 Mar 2014 14:12:36 GMT

Hi,

I have a formula problem with an average calculation I am trying to set up. I have an app where I show different sales in different categories for different people using the following formula:

Count({$<[Sales Category]={"Shoes and other"}>} [Sales Person])

This formula works fine for its calculation, however when I try an rewrite it to an average calculation i cant seem to get it to work. This is the formula I have tried:

Count({$<[Sales Category]={"Shoes and other"}>})/Count(Sales Person)

but I cannot seem to get it to work. Help plz.

Cheers,

Re: Counting average on specific sum

jonathandienst — Thu, 06 Mar 2014 14:19:43 GMT

There are a two syntax problems in your expression. Try this:

Count({$<[Sales Category]={"Shoes and other"}>} [Sales Person])/Count([Sales Person])

HTH

Jonathan

Re: Counting average on specific sum

MK_QSL — Thu, 06 Mar 2014 14:31:06 GMT

COUNT({<[Sales Category]={"Shoes and other"}>} [Sales Person])/COUNT([Sales Person])

Re: Counting average on specific sum

SunilChauhan — Thu, 06 Mar 2014 14:31:26 GMT

Count({$<[Sales Category]={"Shoes and other"}>} [Sales Person])/Count(Total <[Sales Category]>[Sales Person])

Try this

hope this helps

Re: Counting average on specific sum

Thu, 06 Mar 2014 14:49:53 GMT

thansk for the replies, however (sorry for not being clear on this) i do not want the calculation to be on the individual sales person but rather on all total sales/sales category. if i could write it freely i would want it something like:

(all sales for this category A) / (total number of sales people) = average sales category A

Re: Counting average on specific sum

MK_QSL — Thu, 06 Mar 2014 14:53:00 GMT

COUNT({<[Sales Category]={"Shoes and other"}>} [Sales Person])/COUNT(TOTAL [Sales Person])

Re: Counting average on specific sum

SunilChauhan — Thu, 06 Mar 2014 14:55:15 GMT

Count(distinct [Sales Person])/Count(Total <[Sales Category]>[Sales Person])

try this

hope this helps

Re: Counting average on specific sum

sunilkumarqv — Thu, 06 Mar 2014 15:15:58 GMT

HI Nils

Use Aggr Hope you can make work with this function

= Avg( Aggr( count({<Sales Category={"Shoes and other"}>}),Sales Person ))

Re: Counting average on specific sum

Thu, 06 Mar 2014 15:16:44 GMT

i feel that this is close, but it does not really do what i want it to do. i just want the total on category A divided by total number of sales people, something like this

[Count(Sales category = "Shoes and other")] / Count(Sales Person)

why doesnt this work?

Re: Counting average on specific sum

SunilChauhan — Thu, 06 Mar 2014 15:21:58 GMT

Try below

COUNT(Total <Sales category> [Sales Person])/COUNT(TOTAL [Sales Person])

provide Sales category aS DIMENSION

hope this helps

Re: Counting average on specific sum

Thu, 06 Mar 2014 15:23:37 GMT

COUNT({<[Sales Category]={"Shoes and other"}>} [Sales])/COUNT(TOTAL [Sales Person])

Sales mean your field where sales value...

Re: Counting average on specific sum

Thu, 06 Mar 2014 15:46:33 GMT

Im sorry but this is not again accounting for the sales category "Shoes and other", or am I missing something. It doesnt work anyhow, it results in a "1" which isnt correct

Re: Counting average on specific sum

MK_QSL — Thu, 06 Mar 2014 15:49:45 GMT

Looks like we are unable to understand your requirements !!

Can you be more specific in your clarification ? We need to understand first ....

Thanks....

Re: Counting average on specific sum

Thu, 06 Mar 2014 15:57:13 GMT

I think below you want...

You want to count sales category where shoes and other

Divided by total sale person

COUNT({<[Sales Category]={"Shoes and other"}>} [Sales Category])/COUNT(TOTAL [Sales Person])

Vinay

Re: Counting average on specific sum

Thu, 06 Mar 2014 15:58:54 GMT

ok,

i want to take the total sales for the category "shoes" and divide that by the number of sales people i have. so i need the system to go into the database and look in the column "sales category" find "shoes" and take all the sales for that category and sum it up. then divide that by the total number of sales people.

this is why i need some kind of ID like "sales category" = "shoes" otherwise it will not work.

i hope this clarifies everything.

Re: Counting average on specific sum

SunilChauhan — Thu, 06 Mar 2014 16:01:11 GMT

try this

Count({$<[Sales Category]={"Shoes and other"}>} [Sales Person])/Count({<[Sales Category]={*}>}[Sales Person])

it would be great if attached sample with explaination

Re: Counting average on specific sum

MK_QSL — Thu, 06 Mar 2014 16:06:55 GMT

Count({<[Sales Category] = {"Shoes and other"}>}[Sales Person])/Count(Distinct [Sales Person])

Count({<[Sales Category] = {"Shoes and other"}>}[Sales Person])/Count(TOTAL [Sales Person])

Count({<[Sales Category] = {"Shoes and other"}>}[Sales Person])/Count({1} [Sales Person])

Re: Counting average on specific sum

sunilkumarqv — Fri, 07 Mar 2014 04:03:17 GMT

Try this

Hope this gives what you need actually

= Avg( Aggr( count({<Sales Category={"Shoes and other"}>}),Sales Person ))

Re: Counting average on specific sum

Fri, 07 Mar 2014 13:27:02 GMT

Hi all,

I solved this after several different tries:

(Count({$<[Sales Category]={"Shoes and other"}>} [Sales]) / Count (distinct [Sales Person]))

Thank you all fro your input!

Cheers,