4 Replies Latest reply: Jul 25, 2017 9:41 AM by Michael Ostrovsky

Average without zero values

Michael Ostrovsky Jul 23, 2017 1:40 AM

Hi, the question is probably simple, but ...

There are workers and the number of transactions they have сommitted, and there are departments where they work. I need to make the average number of transactions for the department, this is not difficult to do. I do it like this:

Sum (count of transactions) / count (distinct (Worker)))

And everything works, but not the way i need to. I do not want to take into my "average" workers who have't committed any transaction at all. I do not understand where I have to set an exception..

I have found a lot in a forum, but it does not work.

I will be very grateful for any help.

Re: Average without zero values

Andrey Khoronenko Jul 24, 2017 6:18 AM (in response to Michael Ostrovsky)

Hi,

Which flag in data of workers who have't committed any transaction at all. May be fragment of source data and waiting result?

Regards,
Andrey
Like Show 0 Likes (0)

Actions
Re: Average without zero values

Justin Dallas Jul 24, 2017 9:37 AM (in response to Michael Ostrovsky)
Hello Mike,

I tried to get you a solution, but I couldn't figure one out. Somewhere in this statement, probably in the Count clause, you have to have

IF(Aggr(Sum(TransactionCount), WorkerId) > 0)

But I'm not sure if that's the correct statement and where it should go off the top of my head.
Like Show 0 Likes (0)

Actions
Re: Average without zero values

Tim Kendrick Jul 24, 2017 10:35 AM (in response to Michael Ostrovsky)

Have you tried set analysis? Something like:

COUNT({<[Transactions]-={"0"}>} [Transactions]) / COUNT({<[Transactions]-={"0"}>} DISTINCT [Worker])

or

IF([Transactions]>0,COUNT([Transactions]) / COUNT(DISTINCT [Worker]))
Like Show 0 Likes (0)

Actions
- Re: Average without zero values
  
  Michael Ostrovsky Jul 25, 2017 9:41 AM (in response to Tim Kendrick)
  
  Thank you!
  It's really helpful!!
  
  Like Show 1 Like (1)
  
  Actions

Go to original post