Several subsearch with metrics index

[CMartinRuiz]

Hello Community.

I am trying to solve a problem and I can't see a solution. Hope you can help me!

I am working with a metrics index. My final goal is to get average of two metrics, but with two differente filters based on a dimension from that metric index, and get a final calculation from these calculated fields, something like this:

| mstats avg(metric1) as result1 avg(metric2) as result2 where index=my_metric_index AND filter_field=filter_list_1
| mstats avg(metric1) as result3 avg(metric2) as result4 where index=my_metric_index AND filter_field=filter_list_2
| eval Final_Result_1=result3-result1, Final_Result_2=result4-result2

 I also created a search (which I pretend to use as subsearch in the middle of previous search) to get both lists, filter_list_1 and filter_list_2, something like this:

|mcatalog values(values1) as values1 values(values2) as values2 where index=my_metric_index AND filter1 AND filter2 AND filter3 BY values1, values2
{...some modification stuff here...}
| table filter_list_1, filter_list_2

 Both filter_list_1 and filter_list_2 can be returned as a column list or a multivalue field (created with join command from column list).

The chalenge here is how to pass these filter_list_x to both from a subsearch to the main (or precedence) search to use as filter in mstats command.

The best I've got was to make subsearch sent back one of the filter list, named as the field I need to filter in main search with, and subsearch formated field_list (automatically, I don't know how it did) as a bunch of "OR statements with all values of the filter_list filed to use with mstat command.

But I only could o this with one mstat command, not both.

I don't know if I get myself to be well-explained 😋

How can I achieve my "final and complicated" goal? Some like this:

| mstats avg(metric1) as result1 avg(metric2) as result2 where index=my_metric_index AND filter_field=filter_list_1
| mstats avg(metric1) as result3 avg(metric2) as result4 where index=my_metric_index AND filter_field=filter_list_2
[|mcatalog values(values1) as values1 values(values2) as values2 where index=my_metric_index AND filter1 AND filter2 AND filter3 BY values1, values2
{...some modification stuff here...}
| table filter_list_1, filter_list_2]
| eval Final_Result_1=result3-result1, Final_Result_2=result4-result2

Any help will be very appreciated.

Thanks in advance for your help.

Regards,

Carlos M