Oracle Fusion Data Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Fusion Data Intelligence› Oracle Fusion Data Intelligence

Categories

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

Filter Common Value Across Multiple Years

Received Response
168
Views
18
Comments
JobDeLuna
JobDeLuna Rank 5 - Community Champion
edited Aug 13, 2024 7:25PM in Oracle Fusion Data Intelligence

Hi,

I just want to request any help if you have any idea on how can I filter only common values across the years.

For example, I want to see only part numbers that are purchase also in the last year or last 2 years and so on.

"Procurement - Direct Material Spend"."Time"."Fiscal Year Number"

"Procurement - Direct Material Spend"."Voucher Invoice Details"."Part Number"

Tagged:
«1

Answers

  • Mallikarjuna Kuppauru-Oracle
    Mallikarjuna Kuppauru-Oracle Rank 8 - Analytics Strategist

    Hi @JobDeLuna ,

    Did you tried the time-series Functions?

    Regards,

    Arjun

  • Bharat Agrawal
    Bharat Agrawal Rank 3 - Community Apprentice

    Something like this should work for you

    count (distinct
    CASE WHEN "Procurement - Direct Material Spend"."Time"."Fiscal Year Number" =@{YEAR1} OR
    "Procurement - Direct Material Spend"."Time"."Fiscal Year Number" =@{CURRENT_YEAR} = @{YEAR2) THEN
    "Procurement - Direct Material Spend"."Time"."Fiscal Year Number" by "Procurement - Direct Material Spend"."Voucher Invoice Details"."Part Number")
    END

  • JobDeLuna
    JobDeLuna Rank 5 - Community Champion

    Thanks @Bharat Agrawal , however I am having this syntax error. Is there a way to add case when expression, that when Part Numbers in Fiscal Year 2024 is present also in Fiscal Year 2023 then 'YES' else 'NO'

    image.png
  • JobDeLuna
    JobDeLuna Rank 5 - Community Champion

    Hi, @Mallikarjuna Kuppauru-Oracle I haven't tried the time-series Functions and no idea on how can incorporate that. Can you help?

  • Bharat Agrawal
    Bharat Agrawal Rank 3 - Community Apprentice
    edited Jun 2, 2024 9:27AM

    @JobDeLuna

    Yes there is error in the syntax, please place END before BY. Something like this

    count(distinct case when "Procurement - Direct Material Spend"."Time"."Fiscal Year Number" = '2023' or "Procurement - Direct Material Spend"."Time"."Fiscal Year Number" = '2024' THEN "Procurement - Direct Material Spend"."Time"."Fiscal Year Number" END by "Procurement - Direct Material Spend"."Voucher Invoice Details"."Part Number" )

  • JobDeLuna
    JobDeLuna Rank 5 - Community Champion

    Hi, @Bharat Agrawal thanks for your quick response. that result no error. however I want to have a filter that will show only comparable parts.

    https://community.oracle.com/products/oracleanalytics/discussion/21787/filter-the-same-part-number-in-current-year-and-last-year
  • Bharat Agrawal
    Bharat Agrawal Rank 3 - Community Apprentice

    Try this

    CASE WHEN count(distinct case when "Procurement - Direct Material Spend"."Time"."Fiscal Year Number" = '2023' or "Procurement - Direct Material Spend"."Time"."Fiscal Year Number" = '2024' THEN "Procurement - Direct Material Spend"."Time"."Fiscal Year Number" END by "Procurement - Direct Material Spend"."Voucher Invoice Details"."Part Number" )

    1 THEN 'Yes' ELSE 'No' END

  • JobDeLuna
    JobDeLuna Rank 5 - Community Champion

    Hi, @Bharat Agrawal , when I tried this, the result is only "Yes' which should not be.

  • Bharat Agrawal
    Bharat Agrawal Rank 3 - Community Apprentice

    Did you check if the count is returning just one or 2s as well? Make sure your report run for both the years 2023 and 2024

  • JobDeLuna
    JobDeLuna Rank 5 - Community Champion

    Hi, @Bharat Agrawal I checked and 2 is returning as well, and 2 is the right one. I'm trying to filter the 'Actual Spend Amount' within a calculation to compute the Comparable Spend only, however I'm having this error, and I think the Filter function doesn't work on aggregation. Can you help? Thanks a lot!

    image.png