Reporting

Perform math on Data Model Objects CALCULATED Eval Expression

Engager

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(sourcetypesearch) and count(sourcetypebook) 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>
Tags (1)
0 Karma
1 Solution

Champion

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"

View solution in original post

Champion

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"

View solution in original post

Engager

Thanks, it worked.

0 Karma