We're thrilled to have you join our community of analytics enthusiasts and professionals. To enhance your experience and foster meaningful interactions, we encourage you to personalize your profile by setting up a display name and uploading a profile picture. Your display name will be how others recognize and engage with you in discussions, while a profile picture adds a personal touch to your forum presence.
Take a moment to update your profile with a display name and an image representing you. Let's create a vibrant and engaging community together!
Filter Common Value Across Multiple Years
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"
Answers
-
0
-
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")
END1 -
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'
0 -
Hi, @Mallikarjuna Kuppauru-Oracle I haven't tried the time-series Functions and no idea on how can incorporate that. Can you help?
0 -
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" )
0 -
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.
0 -
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
0 -
Hi, @Bharat Agrawal , when I tried this, the result is only "Yes' which should not be.
0 -
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
0 -
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!
0 -
Aggregation will not work within filter, that's the simple rule. Try converting that as well to case statement.
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" ) >= 2 THEN "Actual Spend Amount" ELSE 0 END
0 -
I tried the said calculation however the result is 0 which should not be.
0 -
Please share the entire report XML. I can give a try.
0 -
This is easy if you do in pivot table, two columns represent date from two different years. The last one is a calculated column.
Just click on advanced tab and copy everything from XMLAnalysis window in a text file and share here.
0 -
Hi, @Bharat Agrawal have you check on it? Looking forward to positive response. Thank you! I appreciate your help.
0 -
Sorry bit busy with day job, will look into it later this week or weekend. would you find sharing your linkedin profile here, so I can pm you my contact details.
0 -
No problem. Here's my linkedin profile. Thanks Bharat!
https://www.linkedin.com/in/joven-de-luna-204132a5/
0 -
Great, will ping you
0
