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 on case statement that is using measure

Joe Choueiri-Oracle

Hi experts

Need your help!

I have this case statement that contains measure

CASE WHEN (FILTER( "Fact - WebReg Orders"."Booked Revenue USD" USING ("Order Date"."Fiscal Year" IN ('FY2020'))) IS NOT NULL AND FILTER("Fact - WebReg Orders"."Booked Revenue USD" USING ("Order Date"."Fiscal Year" < 'FY2020')) IS NULL) THEN 'NEW' ELSE 'EXIST' END

and would like to filter to see only the 'NEW' in my analysis but for some reason as you know when it is a measure you dont get a drop down when you filter rather you get free text field but even if I type NEW it is not filtering, I still see both NEW & EXIST.

Any work around?

Thanks

Joe

Find more posts tagged with

Accepted answers

All comments

Jerry Casey

Are you adding unnecessary single quotes in the text field? (they are automatically added in the SQL when using the filter building dialog.) If you check the "Convert filter to SQL" box, does the filter make sense?

This is the SQL that functions correctly on my data (my FY is numeric):

CASE WHEN (FILTER( "Expenditures"."Amount" USING ("Dim Time - Transaction Date"."Federal Fiscal Year" IN (2020))) IS NOT NULL AND FILTER("Expenditures"."Amount" USING ("Dim Time - Transaction Date"."Federal Fiscal Year" < 2020)) IS NULL) THEN 'NEW' ELSE 'EXIST' END = 'NEW'

Jerry Casey

This is the SQL that functions correctly on my data (my FY is numeric):

Jerry Casey

This one functions correctly on my data (my FY is numeric):

Jerry Casey

Are you adding unnecessary single quote in the filter dialog? (it adds them automatically)

If you check the "Convert filter to SQL box, does the result make sense?

This filter functions correctly on my data (my FY is numeric):

Joe Choueiri-Oracle

Thank you Jerry for helping me,

No I dont put single quote...so my filter looks like this in the SQL filter

And if I run the report I still get both the NEW & EXIST

Jerry Casey

Does this box appear in your column formula?
Try checking that and see if it makes a difference.

Jerry Casey

Also, I don't know the granularity of your data, but you may have to do something like:

CASE WHEN (SUM(FILTER( "Expenditures"."Amount" USING ("Dim Time - Transaction Date"."Federal Fiscal Year" IN (2020))) IS NOT NULL AND FILTER("Expenditures"."Amount" USING ("Dim Time - Transaction Date"."Federal Fiscal Year" < 2020) by ..Parent Party Number)) IS NULL) THEN 'NEW' ELSE 'EXIST' END = 'NEW'

I'm shutting down for the holiday weekend. Good Luck!

Jerry

Joe Choueiri-Oracle

It does appear but if I selected for some reason it will create 2 rows per customer, one row for the last 4 years and one row for current year and it will filter on the NEW but it will be false because this customer is not new, they have done business with us.

Jerry Casey

Oh, I think can see it better now. Are you trying to label the Customer as new or existing? If so, you should be testing more like this:

CASE WHEN (SUM(FILTER( "Expenditures"."Amount" USING ("Dim Time - Transaction Date"."Federal Fiscal Year" IN (2020)) by ..Parent Party Number)) IS NOT NULL AND SUM(FILTER("Expenditures"."Amount" USING ("Dim Time - Transaction Date"."Federal Fiscal Year" < 2020) by ..Parent Party Number)) IS NULL) THEN 'NEW' ELSE 'EXIST' END = 'NEW'

Joe Choueiri-Oracle

Thanks Jerry

Adding the SUM & BY to the case statement works - thanks and happy thanksgiving