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

how to build a timestamp function to retrieve data from beginning of fiscal year to current date

Received Response
422
Views
13
Comments
2»

Answers

  • fabryddorf-Oracle
    fabryddorf-Oracle Rank 4 - Community Specialist

    Hi Jerry,

    Thanks for this. As my prompts were not working and I google it, I found that they work not with union queries and also the error message says exctly that, but good to know they work. Will do more testing.

    I am not sure if it is called Bin function, but actually i did so, creating an extra column for channel, market etc.. and created the label 'market', 'channel' etc..

    I have been further working on the filter and came out with this which seems to work for now, as I do not have data for last year.

    FY YTD="Filters"."Business Date" between CAST('01-MAY-' || CAST(YEAR(TIMESTAMPADD(SQL_TSI_YEAR, case when CURRENT_DATE >= CAST('01-MAY-' || CAST(YEAR(TIMESTAMPADD(SQL_TSI_YEAR, 0,CURRENT_DATE)) AS CHAR) AS DATE) then -0 ELSE -1 END ,CURRENT_DATE)) AS CHAR) AS DATE) and current_date

    FY LY YTD= "Filters"."Business Date" between CAST('01-MAY-' || CAST(YEAR(TIMESTAMPADD(SQL_TSI_YEAR, case when CURRENT_DATE >= CAST('01-MAY-' || CAST(YEAR(TIMESTAMPADD(SQL_TSI_YEAR, 0,CURRENT_DATE)) AS CHAR) AS DATE) then -0 ELSE -1 END ,CURRENT_DATE)) AS CHAR) AS DATE) and current_date

    Maybe it is not elegant, but it seems to work. I will try your solution though, so i can learn more.

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    I'm glad you found something that works, but you should really be working with your data base folks to model a correct time dimension, as Gianni recommended. Then, the analysis you're doing would be a simple exercise . Otherwise, it's an inefficient use of your time, and leaves a maintenance mystery for anyone that has to update or tweak your work.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    +1'000'000 that "logic" gets processed for every single row every single query which runs. Performance, maintenance etc all suffer badly from such "solutions".