Perform math on Data Model Objects CALCULATED Eval Expression

ababaei — Tue, 29 Sep 2020 13:56:23 GMT

Hello,

I have created a Data Model which includes a "Root Transaction" Object, BASE SEARCH queries 3 different sourcetypes, I have 5 CALCULATED "Eval Expression" Attributes which I'm using to generate the Pivot

Here is the Search String

| pivot L2B L2B_report count(source_type_search) AS "Count of Search" count(source_type_avail) AS "Count of Availability" count(source_type_book) AS "Count of Book" SPLITROW Dep_Country AS "Dep Country" SPLITROW Des_Country AS "Des Country" SORT 1000000 Dep_Country ROWSUMMARY 0 COLSUMMARY 0 NUMCOLS 0 SHOWOTHER 1

I need to calculate the ratio of "Count of Search" / "Count of Book" and show in the same Pivot using the dashboard.

How can I capture count(source_type_search) and count(source_type_book) as variables to perform division?

Here is the XML

<form>
  <label>L2B</label>
  <fieldset submitButton="false">
    <input type="time" token="L2B_Time_Range">
      <label></label>
      <default>
        <earliest>-7d@h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>| pivot L2B L2B_report count(source_type_search) AS "Count of Search" count(source_type_avail) AS "Count of Availability" count(source_type_book) AS "Count of Book" SPLITROW Dep_Country AS "Dep Country" SPLITROW Des_Country AS "Des Country" SORT 1000000 Dep_Country ROWSUMMARY 0 COLSUMMARY 0 NUMCOLS 0 SHOWOTHER 1</query>
          <earliest>$L2B_Time_Range.earliest$</earliest>
          <latest>$L2B_Time_Range.latest$</latest>
        </search>
        <option name="mapping.choroplethLayer.colorBins">5</option>
        <option name="mapping.choroplethLayer.colorMode">categorical</option>
        <option name="mapping.choroplethLayer.maximumColor">0xDB5800</option>
        <option name="mapping.choroplethLayer.minimumColor">0x2F25BA</option>
        <option name="mapping.choroplethLayer.neutralPoint">0</option>
        <option name="mapping.choroplethLayer.shapeOpacity">0.75</option>
        <option name="mapping.choroplethLayer.showBorder">1</option>
        <option name="mapping.data.maxClusters">100</option>
        <option name="mapping.map.center">(0,0.18)</option>
        <option name="mapping.map.panning">true</option>
        <option name="mapping.map.scrollZoom">false</option>
        <option name="mapping.map.zoom">2</option>
        <option name="mapping.markerLayer.markerMaxSize">50</option>
        <option name="mapping.markerLayer.markerMinSize">10</option>
        <option name="mapping.markerLayer.markerOpacity">0.8</option>
        <option name="mapping.showTiles">1</option>
        <option name="mapping.tileLayer.maxZoom">7</option>
        <option name="mapping.tileLayer.minZoom">0</option>
        <option name="mapping.tileLayer.tileOpacity">1</option>
        <option name="mapping.type">marker</option>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">pie</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">right</option>
        <option name="list.drilldown">full</option>
        <option name="list.wrap">1</option>
        <option name="maxLines">5</option>
        <option name="raw.drilldown">full</option>
        <option name="rowNumbers">false</option>
        <option name="table.drilldown">all</option>
        <option name="table.wrap">1</option>
        <option name="type">list</option>
        <option name="wrap">true</option>
        <option name="dataOverlayMode">heatmap</option>
        <option name="drilldown">cell</option>
        <option name="count">50</option>
      </table>
    </panel>
  </row>
</form>

Re: Perform math on Data Model Objects CALCULATED Eval Expression

rjthibod — Wed, 03 May 2017 20:22:59 GMT

You just need to change the field names so you can play with them, like the following. You may want to consider doing a sort at the end to fit your needs.

| pivot L2B L2B_report count(source_type_search) AS count_search count(source_type_avail) AS count_avail count(source_type_book) AS count_book SPLITROW Dep_Country AS "Dep Country" SPLITROW Des_Country AS "Des Country"
| eval ratio = round(count_search / count_book, 1)
| rename count_search as "Count of Search", rename count_avail as "Count of Availability",  count_book as "Count of Book"

Re: Perform math on Data Model Objects CALCULATED Eval Expression

ababaei — Wed, 03 May 2017 21:00:49 GMT

Thanks, it worked.

topic Re: Perform math on Data Model Objects CALCULATED Eval Expression in Reporting

Perform math on Data Model Objects CALCULATED Eval Expression

Re: Perform math on Data Model Objects CALCULATED Eval Expression

Re: Perform math on Data Model Objects CALCULATED Eval Expression