Canvas - Error in populate dynamic variable/parameter

Elastic Stack Kibana
1

Hi !
I'm trying to create a Data Table in canvas.
I would like to add 3 columns in my table:

  • Asset
  • Volumes (as a count of record)
  • % On Total (as Asset Volumes/Total Volumes)

I create the follow script in espression editor, but my espression failed with the message "[essql] > Can not cast 'datatable' to any of string,number, boolean".

How can I change my espression ? There is a way to populate a variable or a parameter with dynamic value? (In my case with the count of total row ? )

filters
| var_set name="Total" value={
    essql query="SELECT count(*) FROM \"index-pattern*\" where Date > dateadd('MM',-1,curdate()) and ReportDate < dateadd('DD',-2,curdate()) "
  }
| essql 
  query="select Asset,
count(*) as Volumi,
count(*)/? *100 as PercOnTotal
from \"index-pattern*\"
where 
Date > dateadd('MM',-1,curdate()) and Date < dateadd('DD',-2,curdate()) 
group by Asset" parameter={var "Total"}
| table
| render

Thanks,
Chiara

2

Hi @Chiaaa ,

How about a different approach like below ?

filters
| essql 
  query="select Asset,
count(*) as Volumi
from \"index-pattern*\"
where 
Date > dateadd('MM',-1,curdate()) and Date < dateadd('DD',-2,curdate()) 
group by Asset"
|staticColumn  name=total_records value={math "sum(Volumi)"}
|mapColumn name=PercOnTotal expression={math "(Volumi/total_records)*100"}
|columns include=Asset,Volumi,PercOnTotal
| table
| render
3

Thank you very much @preetish_P !

4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.