Forum Stats

  • 3,874,256 Users
  • 2,266,715 Discussions
  • 7,911,787 Comments

Discussions

Previous 3 Months Data from a Prompt.

I am trying to go back 3 months from a selected month via prompt using this formula

timestampadd(sql_tsi_month, -3, EVALUATE('TO_DATE(%1,%2)' as timestamp,'01/'||substring("Time"."Month",8,2)||'/'||substring("Time"."Month",1,4), 'dd/mm/yyyy'))

OR

TIMESTAMPADD(SQL_TSI_MONTH, -3, substring("Time"."Month",8,2)||'/'||'01'||'/'||substring("Time"."Month",1,4)AS DATE))

This formula is working in 'edit formula' for a column but not working for filter. In the edit formula by writing this only the column headers are going back 3 months but the data is not. I guess this is how the edit formula works. So i tried to put this in Filter. But it is throwing an error.

ORA-01858: a non-numeric character was found where a numeric was expected at OCI call OCIStmtFetch. [nQSError: 17012] Bulk fetch failed. (HY000).

i need help soon.


Thanks

Answers

  • SriniVEERAVALLI
    SriniVEERAVALLI Member Posts: 8,539 Gold Crown
    edited May 8, 2013 11:25AM
    Pull date column in the criteria and Filter Icon->Advanced->Convert this filter to SQL
    add he below lines, MM is my month presentation variable. Assuming your month format is something like YYYY / MM

    Time.Date >= TIMESTAMPADD(SQL_TSI_MONTH, -2, cast(substring('@{MM}{2013 / 01}',8,2)||'/'||'01'||'/'||substring('@{MM}{2013 / 01}',1,4) as date))
    AND
    Time.Date <= TIMESTAMPADD(SQL_TSI_MONTH, 0, cast(substring('@{MM}{2013 / 01}',8,2)||'/'||'01'||'/'||substring('@{MM}{2013 / 01}',1,4) as date))

    This should work

    Edited by: Srini VEERAVALLI on May 8, 2013 10:24 AM
This discussion has been closed.