in Panel result getting unwanted "NaN" in search query getting correct result.

[anjneesharma]

in my Splunk query result getting result as "NaN" but i will run my query in search i will get correct result not NaN Please help me regarding this.

[aruncozy]

Hi Niket, with respect to the above Query i am continuing where Anjnee had left.

Below is the sample xml for that particular panel:

    <panel>
      <table>
        <search id="monthly_summary" ref="cop_monthly_summary_savedsearch"></search>
      </table>
    </panel>

<row depends="$ou_level$">
    <panel>
      <table id="poc_monthly2">
        <title>$sgccr_year$ YTD COP contract performance – Application Services - Summary at OU (as of $sgccr_month$ YTD)</title>
        <search base="target_master">
          <query>| table geography og ou   contract_number contract_name primary_csg master_client_name |join contract_number [|savedsearch cop_sgccr_sg_savedsearch | where service_group="APPLICATION OUTSOURCING" OR service_group="SYSTEM INTEGRATION" | stats sum(ytd_nr) as r_ytd_nr_curr sum(ytd_cci) as ytd_cci_curr by contract_number | fillnull value=0 ]| join contract_number type=outer [|savedsearch cop_previousyr_sgccr_sg_savedsearch | where service_group="APPLICATION OUTSOURCING" OR service_group="SYSTEM INTEGRATION" | stats sum(q1_nr) AS "q1_nr" sum(q1_cci) AS "q1_cci" sum(q2_nr) AS "q2_nr" sum(q2_cci) AS "q2_cci" sum(q3_nr) AS "q3_nr" sum(q3_cci) AS "q3_cci" sum(q4_nr) AS "q4_nr" sum(q4_cci) AS "q4_cci" by contract_number | fillnull value=0 | eval q1_cci=if(isnull(q1_cci),0,q1_cci) |eval q2_cci=if(isnull(q2_cci),0,q2_cci)| eval q3_cci=if(isnull(q3_cci),0,q3_cci)| eval q4_cci=if(isnull(q4_cci),0,q4_cci)| eval q1_nr=if(isnull(q1_nr),0,q1_nr) |eval q2_nr=if(isnull(q2_nr),0,q2_nr)| eval q3_nr=if(isnull(q3_nr),0,q3_nr)| eval q4_nr=if(isnull(q4_nr),0,q4_nr) | eval fy_cci_prev = q1_cci + q2_cci + q3_cci + q4_cci | eval fy_nr_prev = q1_nr + q2_nr + q3_nr + q4_nr | eval forecast_month="1"|eval current_quarter_no=case(forecast_month=9 OR forecast_month=10 OR forecast_month=11,1,forecast_month=12 OR forecast_month=1 OR forecast_month=2,2,forecast_month=3 OR forecast_month=4 OR forecast_month=5,3,forecast_month=6 OR forecast_month=7 OR forecast_month=8,4) | eval ytd_nr_prev=case(current_quarter_no=1,q1_nr,current_quarter_no=2,q1_nr+q2_nr,current_quarter_no=3,q1_nr+q2_nr+q3_nr,current_quarter_no=4,q1_nr+q2_nr+q3_nr+q4_nr) | eval ytd_cci_prev=case(current_quarter_no=1,q1_cci,current_quarter_no=2,q1_cci+q2_cci,current_quarter_no=3,q1_cci+q2_cci+q3_cci,current_quarter_no=4,q1_cci+q2_cci+q3_cci+q4_cci)| fields - current_quarter_no forecast_month  | fields - q* yearofpublish_sgccr ytd_nr ytd_cci] |stats sum(fy_cci_prev) as fy_cci_prev sum(fy_nr_prev) as fy_nr_prev sum(r_ytd_nr_curr) as r_ytd_nr_curr sum(ytd_cci_curr) as ytd_cci_curr sum(ytd_cci_prev) as ytd_cci_prev sum(ytd_nr_prev) as ytd_nr_prev by ou| eval fiscaly_prev = round(fy_cci_prev/fy_nr_prev *100,1) | eval yeartd_curr = round(ytd_cci_curr/r_ytd_nr_curr *100,1) | eval yeartd_prev = round(ytd_cci_prev/ytd_nr_prev *100,1) | fields - fy* ytd* |  eval ytd_vs_prev = yeartd_curr-yeartd_prev|eval r_ytd_nr_curr = round(r_ytd_nr_curr,0)  | eval r_ytd_nr_curr = tostring(r_ytd_nr_curr, "commas")|search ou="$primary_ou$"|table ou fiscaly_prev r_ytd_nr_curr yeartd_curr yeartd_prev ytd_vs_prev |rename fiscaly_prev as "AS Full Prev FY CCI%" r_ytd_nr_curr as "AS YTD actuals FY18 NR" yeartd_curr as "AS YTD actuals FY18 CCI%" yeartd_prev as "AS YTD Prev FY CCI%" ytd_vs_prev as "AS YTD FY18 CCI% actuals vs AS YTD Prev FY CCI%" contract_number as "Contract" ou as "Primary OU"</query>
          <earliest>0</earliest>
          <latest></latest>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="count">10</option>
      </table>
    </panel>
  </row>

The savedsearch had the below code:

| pivot cop_datapack Target_Master_Control_List latest(month) AS "month" latest(Status) AS "Status" latest(contract_name) AS "contract_name" latest(cop_owner_name) AS "cop_owner_name" latest(ao) AS "ao" latest(bpo) AS "bpo" latest(cop_wave) AS "cop_wave" latest(engagement_id) AS "engagement_id" latest(engagement_name) AS "engagement_name" latest(hri_list_status) AS "hri_list_status" latest(ic) AS "ic" latest(io) AS "io" latest(og) AS "og" latest(master_client_name) AS "master_client_name" latest(master_contract_name) AS "master_contract_name" latest(mc) AS "mc" latest(opera_contract) AS "opera_contract" latest(primary_csg) AS "primary_csg" latest(revised_rbe) AS "revised_rbe" latest(sc) AS "sc" latest(si) AS "si" latest(tc) AS "tc" latest(OU) AS "ou" latest(delivery_coach) AS "delivery_coach" latest(delivery_lead_name) AS "delivery_lead_name" latest(target_cci) as target_cci SPLITROW geography AS geography SPLITROW contract_number AS contract_number|where Status="Active"

[niketn]

@anjneesharma what is your query, which visualization are you using and can you post some sample data?

NaN could be because of several reasons, for example... if you mess with the time format and try to plot a chart with it you would get an error in chart while stats will show you HH:MM:SS time with count. Following is a run anywhere search.

|  makeresults
|  eval _time=strftime(_time,"%H:%M:%S")
|  eval count=5
|  chart max(count) over _time

[anjneesharma]

@niketnilay

If i will open my same in query in search result is coming correct i.e insead of NAN real value comes.

[niketn]

How are you presenting the results? Table or any other visualization?

Can you post the simple XML from the panel?

Also what is the query in saved search...cop_monthly_summary_as_savedsearch

[anjneesharma]

@niketn plz find my query bellow

|savedsearch cop_monthly_summary_as_savedsearch|stats sum(fy_cci_prev) as fy_cci_prev sum(fy_nr_prev) as fy_nr_prev sum(r_ytd_nr_curr) as r_ytd_nr_curr sum(ytd_cci_curr) as ytd_cci_curr sum(ytd_cci_prev) as ytd_cci_prev sum(ytd_nr_prev) as ytd_nr_prev by ou| eval fiscaly_prev = round(fy_cci_prev/fy_nr_prev 100,1) | eval yeartd_curr = round(ytd_cci_curr/r_ytd_nr_curr *100,1) | eval yeartd_prev = round(ytd_cci_prev/ytd_nr_prev *100,1) | fields - fy ytd* | eval ytd_vs_prev = yeartd_curr-yeartd_prev|eval r_ytd_nr_curr = round(r_ytd_nr_curr,1) | eval r_ytd_nr_curr = tostring(r_ytd_nr_curr, "commas")| search ou="PRD AAPAC OU" | table ou fiscaly_prev r_ytd_nr_curr yeartd_curr yeartd_prev ytd_vs_prev |rename fiscaly_prev as "AS Full Prev FY CCI%" r_ytd_nr_curr as "AS YTD actuals FY18 NR" yeartd_curr as "AS YTD actuals FY18 CCI%" yeartd_prev as "AS YTD Prev FY CCI%" ytd_vs_prev as "AS YTD FY18 CCI% actuals vs AS YTD Prev FY CCI%" contract_number as "Contract" ou as "Primary"

Here ou as "Primary" is giving result as "NaN"

Please help me regarding this

[niketn]

You seem to have string data in ou based on your search query. So, this is indeed non-numeric data.

search ou="PRD AAPAC OU"

If you output the result in Table there should be no issues.

Also while posting code or sample data on Splunk Answers use to code button i.e. 101010 or shortcut Ctrl+K

| savedsearch cop_monthly_summary_as_savedsearch 
| stats sum(fy_cci_prev) as fy_cci_prev sum(fy_nr_prev) as fy_nr_prev sum(r_ytd_nr_curr) as r_ytd_nr_curr sum(ytd_cci_curr) as ytd_cci_curr sum(ytd_cci_prev) as ytd_cci_prev sum(ytd_nr_prev) as ytd_nr_prev by ou 
| eval fiscaly_prev = round(fy_cci_prev/fy_nr_prev*100,1) 
| eval yeartd_curr = round(ytd_cci_curr/r_ytd_nr_curr*100,1) 
| eval yeartd_prev = round(ytd_cci_prev/ytd_nr_prev*100,1) 
| fields - fy ytd 
| eval ytd_vs_prev = yeartd_curr-yeartd_prev 
| eval r_ytd_nr_curr = round(r_ytd_nr_curr,1) 
| eval r_ytd_nr_curr = tostring(r_ytd_nr_curr, "commas") 
| search ou="PRD AAPAC OU" 
| table ou fiscaly_prev r_ytd_nr_curr yeartd_curr yeartd_prev ytd_vs_prev 
| rename fiscaly_prev as "AS Full Prev FY CCI%" r_ytd_nr_curr as "AS YTD actuals FY18 NR" yeartd_curr as "AS YTD actuals FY18 CCI%" yeartd_prev as "AS YTD Prev FY CCI%" ytd_vs_prev as "AS YTD FY18 CCI% actuals vs AS YTD Prev FY CCI%" contract_number as "Contract" ou as "Primary"