Use a Calculated Aggregate in HighCharts JaspserStudio

0

I need to find a way to use a calculated aggregates as the series value in HighCharts. I have found the solution for Crosstabs, but there doesn't seem to be the same type of solution for HighCharts.

For example, a heatmap needs to show the avg price for a product by store. The calculation should be SUM(SALE_PRICE) OVER (PARTITION BY PRODUCT,STORE) / SUM(ORDER_QTY) OVER (PARTITION BY PRODUCT,STORE). I can get the two sum values correct, create a hidden measure with these fields where the calculation is set to sum. But I can't find anyway to get the rate calculated correctly. Any measure defined in a high chart doesn't have the standard properties to set it's evaluation time. If I create three variables, the sum isn't happening at the correct intersection.

In crosstabs, I created the two sum measures for each intersection, and then in the cell display I did the TOTAL_PRICE/TOTAL_QTY as the display value. I am wondering if there is a way to do this same kind of behavior for a series in a high chart. I have the two metrics and have been trying to find a way to supersede the data.value property of a displayed series with this rate calculation but to no avail.

<multiAxisMeasure name="td" class="java.lang.Double" calculation="Sum">
<labelExpression><![CDATA["td"]]></labelExpression>
<valueExpression><![CDATA[$F{Fact.QUANTITY}]]></valueExpression>
</multiAxisMeasure>
<multiAxisMeasure name="cd" class="java.lang.Double" calculation="Sum">
<labelExpression><![CDATA["cd"]]></labelExpression>
<valueExpression><![CDATA[$F{Fact.SALE_PRICE}]]></valueExpression>
</multiAxisMeasure>
</multiAxisData>
<hc:series name="Percent Booked">
<hc:contributor name="SeriesProperty">
<hc:contributorProperty name="data.value" valueType="Bucket">
<hc:valueExpression><![CDATA["function(){return this.point.cd/this.point.td }"]]></hc:valueExpression>
</hc:contributorProperty>
</hc:contributor>
<hc:contributor name="SeriesItemProperty">
<hc:contributorProperty name="td" valueType="Measure" value="td"/>
<hc:contributorProperty name="cd" valueType="Measure" value="cd"/>
</hc:contributor>
</hc:series>
<hc:series name="td" visible="false"/>
<hc:series name="cd" visible="false"/>

 

craig.linderoth's picture
Joined: Jan 14 2015 - 5:27am
Last seen: 5 years 6 months ago

0 Answers:

No answers yet
Feedback