Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.