How do I get data (value) from a table in one panel to perform a calculation on another table panel in the same dashboard?

[BobKimata]

Hi guys

Each table is in a different panel and contains different searches from different database tables. I would like to use data from the table in the first panel to perform a calculation with data in a table in a panel below.
Note: It is not a drill down. The tables contain totally different data.

Cheers
Bob

[somesoni2]

Try this for your search for panel 2

| dbquery AdWordsROI limit=1000 "SELECT   site_id,tracking_id, date_pst, sum(total_subscribers) as TotalSubscribers,sum(total_sales) as TotalSales ,sum(`total_amount_usd`) as TotalAmount from revenue_data WHERE date_pst = '2015-05-04' and site_id= 'AR' group by tracking_id" | join site_id [| dbquery AdWordsROI "SELECT ClientName as site_id, sum(Cost) as Cost FROM account_performance  where `Day` between from_unixtime($time_range1.earliest$,'%y-%m-%d')and from_unixtime($time_range1.latest$,'%y-%m-%d')  group by ClientName"] | eval Percentage=round((TotalAmount-Cost)*100/TotalAmount,2)

[BobKimata]

I have looked into the append command but it doesnt seem like the command to use in this case.

[BobKimata]

The first panel is drawing its search from an SQL query as follows:

<row>
        <panel>
        <table id = "accounts">
        <title>Click account for more details </title>
        <search>
          <query>| dbquery AdWordsROI "SELECT ClientName, sum(Impressions) as Impressions, sum(Clicks) as Clicks, sum(Cost) as Cost FROM account_performance  where `Day` between from_unixtime($time_range1.earliest$,'%y-%m-%d')and from_unixtime($time_range1.latest$,'%y-%m-%d')  group by ClientName" </query>
        </search>
        </table>
    </panel>
  </row>

The panel below this one draws data from a different SQL query ie:

<row>
    <panel>

      <table>
        <title>Filtered</title>
        <search>
          <query>| dbquery AdWordsROI limit=1000 "SELECT   site_id,tracking_id, date_pst, sum(total_subscribers) as TotalSubscribers,sum(total_sales) as TotalSales ,sum(`total_amount_usd`) as TotalAmount from revenue_data WHERE date_pst = '2015-05-04' and site_id= 'AR' group by tracking_id" 
          </query>
        </search>
        <option name="count">5</option>
      </table>
    </panel>
  </row>

I would like to pick data from the 'cost' field in the first panel to do an eval with the 'total amount' field in the second panel. Basically to subtract cost from amount and get a percentage of it and display it in a new column in the second panel.

Cheers
Bob

[somesoni2]

What's the common field between two results? It does seems to have more than 1 rows (in both searches) so you'll need a common field to subtract correct cost from correct TotalAmount.

[BobKimata]

the field that has common data is the 'ClientName' in the first table and 'site_id' in the second table.

[somesoni2]

Since, it's not a drilldown, you might have to use the query from first panel in the 2nd panel query. How it's going to be used is depends upon your calculation. Would help you if you can provide more details , may be sample queries..

[rphillips_splk]

have you looked into using the append search command?
http://docs.splunk.com/Documentation/Splunk/6.2.5/SearchReference/Append