Hello,
how can I exploit the "dropdown column" : for example i am showing a metric (costs), and I want to display the "costs" of different "categories".
When it's on 'any' it gives me a correct value which is the sum of the costs of different categories.
However, when I choose one of the "categories" , It is all the time giving me 0.
So, can you tell me please how can I proceed?
Hi wadhah, it sounds like something is mismatched between the value and filter columns you are deriving for the dropdown column. Could you copy/paste the expression you are generating?
Hello @tims,
Thank you for the update.
Here's my metric's expression:
filters
| essql
query="SELECT SUM(Costs)
FROM "azureconsumption*"
WHERE MONTH(usageEnd)=MONTH(CURRENT_DATE)
"
| math "SUM_Costs_"
| metric "Euros"
metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center" lHeight=48}
labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"}
| render
And Here's the the filter'S expression:
essql
query="SELECT ConsumedService
FROM "azureconsumption*"
GROUP BY ConsumedService"
| dropdownControl valueColumn="ConsumedService" filterColumn="ConsumedService"
| render
Ok, so I think that you need to update your Costs query to be grouped by ConsumedService so that the value that you are passing in via filterColumn will actually have a corresponding column in the Costs query. If you visualize your Costs query as a datatable you will see that right now it is only returning the total sum not broken out by ConsumedService.
Here is a similar question that was posted a while back that may give you a better idea of some things to try.
Hey @tims thank you for your response.
However I disagree about the fact concerning datatable, because if i pass these sql commands:
SELECT ConsumedService, SUM(Costs)
FROM "azureconsumption*"
WHERE MONTH(usageEnd)=MONTH(CURRENT_DATE())
GROUP BY ConsumedService
I get myself a table showing me each ConsumedService and its amount of costs during this month.
so if you use that query, and you name your SUM(Costs) column "sumCosts" or something and then use that column name in the filterColumn, it still returns 0?
Yes @tims it still returns 0
Hey @wadhah, ok I've attempted to re-create your issue and I think I have using some sample data. It looks like you may be encountering this bug: https://github.com/elastic/kibana/issues/26497
At this point it sounds like your queries are correct and I could reproduce the dataset coming back as 0 whenever I had categories with spaces or mixed case values, so if your ConsumedService value is a string with either spaces or mixed case then it is filtering incorrectly because of this bug.
I used an other field where its values are "strings" with only lowercases and still facing the same problem
@Joe_Fleming
Hey @tims I found a solution : I used ConsumedService.keyword and it worked, how is that?
And if you please I have an other question : I have other charts with which I am querying other data from other indexes, is there a way to avoid getting them impacted by the dropdown column? Since whenever I choose one of the categories, i loose these charts (if they are metrics they return 0 .....)
1 Like
Hi @wadhah, that was going to be my next suggestion. ConsumedService is an analyzed field so if you have values with multiple words it will be broken up when evaluating the filter. Using keyword ensures that it is doing the comparison against the whole string.
Unfortunately, right now the filters are applied to the entire canvas but the ability to apply the filter to specific elements is in the roadmap.
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.