Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
covenant_bi
Creator
Creator

Geometric Mean calculation won't calculate higher product due to limitations

Hello, I am trying to calculate a geometric mean in Qlik Sense similar to the post here 

But it basically using this formula here:

=pow(exp(sum(log( YOURFIELD))),1/count( YOURFIELD) )

It works very well for 90% of my app, except when the exp(sum(log( YOURFIELD)) product is too high for Qlik to calculate, and it results in an error and the calculation doesn't finish and I get the dreaded "-"

When I export that part of the data set to Excel and run just a regular PRODUCT on the values, Excel doesn't like it either.  Oddly, the GEOMEAN function works in Excel and returns an expected value.   

Any ideas on how to force Qlik to calculate higher values?

1 Solution

Accepted Solutions
covenant_bi
Creator
Creator
Author

I figured out the way to do this, basically the idea is to run something like this

exp(avg(log(your field)))

 

Inspiration came from these two pages. 

http://timothychenallen.blogspot.com/2006/03/sql-calculating-geometric-mean-geomean.html

https://datagenetics.com/blog/april42014/index.html

While the links focus on Excel/SQL the same idea can be applied to Qlik Sense. I validated that the same results come from using the same formula in Excel using the same dataset.

View solution in original post

1 Reply
covenant_bi
Creator
Creator
Author

I figured out the way to do this, basically the idea is to run something like this

exp(avg(log(your field)))

 

Inspiration came from these two pages. 

http://timothychenallen.blogspot.com/2006/03/sql-calculating-geometric-mean-geomean.html

https://datagenetics.com/blog/april42014/index.html

While the links focus on Excel/SQL the same idea can be applied to Qlik Sense. I validated that the same results come from using the same formula in Excel using the same dataset.