nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Filter Common Value Across Multiple Years

JobDeLuna

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"

Find more posts tagged with

Analysis

Filters

FDI

Fusion AI Data Platform

Accepted answers

All comments

Mallikarjuna Kuppauru-Oracle

Hi @JobDeLuna ,

Did you tried the time-series Functions?

Regards,

Arjun

Bharat Agrawal

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

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'

JobDeLuna

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

@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

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

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

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

Bharat Agrawal

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

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!