eCommerce demo percentages are not between 0 and 100%

[alexander-marquardt]

Kibana 7.4:

Describe the bug:
The eCommerce kibana demo shows "percentages" that do not make sense such as 522% and 478%. After looking at the expression, I believe this is related to the update to esdocs default number of documents returned (see update: #41604)

Steps to reproduce:
Open the demo and look at the numbers beside the MALE and FEMALE pants drawing.

Expected behavior:
Percentages between 0 and 100%.

Screenshots (if relevant):

[alexander-marquardt]

I believe that the following expression would address the above issue:

esdocs index="kibana_sample_data_ecommerce" sort="order_date, desc" fields="customer_gender" 
| mapColumn "maleCount" exp={getCell "customer_gender" 
| if {compare to="MALE"} then=1 else=0} 
| math "round(100 * sum(maleCount) / count(maleCount))" 
| markdown {context} "%" font={font family="Avenir" size=48 align="center" color="#f8bd4a" weight="normal" underline=false italic=false}

[elasticmachine]

Pinging @elastic/kibana-canvas (Team:Canvas)

[alexander-marquardt]

Page 2 of the eCommerce workpad has the same issue

[alexander-marquardt]

Note that the original query and the fix that I proposed above both give inaccurate results, as they only pull 1000 documents back from Elasticsearch and then leverage the browser to compute the numerator and denominator based on those 1000 documents. If more accurate results are desired, then the example could be written as follows which uses elasticsearch to provide an accurate count for the numerator and denominator:

essql "SELECT COUNT(*) as male_count FROM \"kibana_sample_data_ecommerce\" WHERE customer_gender = 'MALE' "
| math {
 string "male_count/" 
   {filters group="fake-nonexistant-group"  | essql "SELECT COUNT(*) as denominator FROM \"kibana_sample_data_ecommerce\" "| math  "denominator"}
 }
| math "round(100*value,2)"
| markdown {context} "%" font={font family="Avenir" size=48 align="center" color="#f8bd4a" weight="normal" underline=false italic=false}

I'd like to remove the filters from in front of the 2nd essql statement, but this breaks the statement -- and so I have instead added a fake filter that should never impact the data

[nmoreaud]

That seems to be a counter intuitive way to compute metrics.
I am discovering the product and I feel it is missing some key features.

What about implementing the concept of local/global variables ? Global to the canvas, or local to the element. It would simplify a lot this kind of basic computation.
Or we could use SQL subqueries to compute the result ? (select count() where gender = 'MALE') / (select count())