Oracle Analytics and AI

MichelleG

We have an RTF template that is pulling the start and end date of an element name. We want to get the minimum start date and maximum end date of the Regular Retro. What we’re doing is we get the values of start and end date then format it to remove the ‘-‘ then apply the min/max then format it to ‘yyyy-MM-dd’ but cannot get the correct output. Any ideas on how to fix this issue?

Expected output:

Description – Start Date – End Date

Regular Retro – 2024-06-16 – 2024-07-13

Shift Diff Hrs@7% Retro – 2024-06-30 – 2024-07-13

Sample xml:

<GLB_PAY_ARCH_PR_HOURS_X_RATE>

<REPORTING_NAME>Regular Retro</REPORTING_NAME>

<ORIGINATING_START_DATE>2024-06-16</ORIGINATING_START_DATE>

<ORIGINATING_END_DATE>2024-06-29</ORIGINATING_END_DATE>

</GLB_PAY_ARCH_PR_HOURS_X_RATE>

<GLB_PAY_ARCH_PR_HOURS_X_RATE>

<REPORTING_NAME>Regular Retro</REPORTING_NAME>

<ORIGINATING_START_DATE>2024-06-30</ORIGINATING_START_DATE>

<ORIGINATING_END_DATE>2024-07-13</ORIGINATING_END_DATE>

</GLB_PAY_ARCH_PR_HOURS_X_RATE>

Start Date code:

<xsl:variable name="var1">

<xsl:for-each select="current-group()/ORIGINATING_START_DATE">

<xsl:element name="T_ORIGINATING_START_DATE">

<xsl:value-of select="xdoxslt:replace(substring(.,1,10),'-','')" /> </xsl:element>

</xsl:for-each>

</xsl:variable>

<xsl:value-of xdofo:ctx="inlines" select="min($var1/T_ORIGINATING_START_DATE)"/>

<xsl:value-of xdofo:ctx="inlines" select="concat(substring($var1/T_ORIGINATING_START_DATE,1,4),'-',substring($var1/T_ORIGINATING_START_DATE,5,2),'-',substring($var1/T_ORIGINATING_START_DATE,7,2))"/>

End Date code:

<xsl:variable name="var1">

<xsl:for-each select="current-group()/ORIGINATING_END_DATE">

<xsl:element name="T_ORIGINATING_END_DATE">

<xsl:value-of select="xdoxslt:replace(substring(.,1,10),'-','')" /> </xsl:element>

</xsl:for-each>

</xsl:variable>

<xsl:value-of xdofo:ctx="inlines" select="max($var1/T_ORIGINATING_END_DATE)"/>

<xsl:value-of xdofo:ctx="inlines" select="concat(substring($var1/T_ORIGINATING_END_DATE,1,4),'-',substring($var1/T_ORIGINATING_END_DATE,5,2),'-',substring($var1/T_ORIGINATING_END_DATE,7,2))"/>

Result:

Dimple-Nagesh-Support-Oracle

Hello @MichelleG,

Are you saying the date column is also printing DATE & Time, if yes, you can edit the column properties in the data model to show Date Only. Kindly test the same and let me know if that helps.

Thanks,
Dimple N