4 Replies Latest reply on Oct 15, 2018 8:02 PM by meenu choudhary

# SUM(IF Value < 2000)

I am looking for a solution to a theoretically extremely simple problem.
In Numbers, Excel, Python or R it takes me 5 seconds, but in Tableau it seems to be more complicated. I have no idea why.

A column [distance] with e.g. 10 values between 1000 and 5000. I want to calculate the average of the values between 1000 and 4000. So I ignore all values which are > 4000.

Then I calculate the sum of all values in the range 1000 to 4000. Then I count the number of values between 1000 and 4000.

I divide the sum and the number to get the average. Actually the simplest of simplest.

"IF [distance] < 5000 THEN COUNT([distance]) ELSE 0 END" -> ERROR

This is so embarrassing and simple - but Tableau cannot, for example, divide a calculated field [sum(distance)] by [count(distance)].

All known concepts known as statisticians do not work with Tableau - especially if you check the calculations in R or Python.

Can someone give me a hint from which side to think in order to get results that work?

• ###### 1. Re: SUM(IF Value < 2000)

Perhaps I'm missing something from your explanation but could you not just use

COUNT(IF [Distance] < 5000 THEN 'whatever you want to count' END)

• ###### 2. Re: SUM(IF Value < 2000)

Helllo,

You can try below logic:

COUNT ( IF [distance] < 5000 THEN [distance] END )

• ###### 3. Re: SUM(IF Value < 2000)

No, its only about "COUNT". No "sum", no "avg", no "mean".
Im looking only for the super-easy option to count all entries from < 4000 to >1000.
"COUNT ( IF [distance] < 5000 THEN [distance] END )" gives an ERROR: "There is no option mix int & strings".