Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Date comparison in graph - xml publisher

Received Response
41
Views
3
Comments
Zshaikh
Zshaikh Rank 4 - Community Specialist
in Oracle Analytics Cloud and Server

Summary

Date comparison in graph - xml publisher

Content

Hi,

i need to create a line graph showing some field values against date.

i need to test if date value is greater than current month then line should stop displaying further.

e.g. A'cumulative line(red) should disappear after jun 17.

image

PFB, code snippet

<RowData>

<xsl:value-of select="xdoxslt:set_variable($_XDOCTX,'ac', 0)" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" />

<xsl:for-each-group select="current-group()" group-by="MON_YY" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema"

exclude-result-prefixes="xs">

<Cell>

<xsl:variable name="dateValue" select="current-group()/ROUNDED_DATE"/>

<xsl:choose>

<xsl:when test="xs:date($dateValue) &lt;= current-date()">

<xsl:value-of select="sum(current-group()/ACTUALS[.!='' and .!='0' and ../PROP_NAME='Actuals'])+xdoxslt:get_variable($_XDOCTX,'ac')" />

</xsl:when>

</xsl:choose>

<xsl:value-of select="xdoxslt:set_variable($_XDOCTX,'ac',sum(current-group()/ACTUALS[.!='' and .!='0' and ../PROP_NAME='Actuals'])+xdoxslt:get_variable($_XDOCTX,'ac'))" />

</Cell>

</xsl:for-each-group>

</RowData>

any solutions???????

Best Regards,

Shaikh

Answers

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    Do it in the query ... any value above your date set to NULL ....

  • Zshaikh
    Zshaikh Rank 4 - Community Specialist

    Hi Thomas,

    Thanks for responding.

    i tried setting null values for future dates but we are using sum() to get cumulative value hence it gives 0 instead of Null.

    any inputs????

    Regards,

    Zeenat

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    evaluate the date if less than or equal to your 'target' date do the sum else NULL ... don't sum a null

    OR

    sum it up for every date (and get the flat line like you have) then wrap that sql with a case when the date >= target then use the null and not the re-summed value

    wrapping a SQL:

    SELECT x.DATE,

    CASE WHEN x.DATE >= 'DD-MON-YYYY' THEN NULL ELSE VAL END VAL

    FROM (

    SELECT DATE, SUM(VALUE) VAL FROM TABLE GROUP BY DATE) x