Oracle Analytics Cloud and Server

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

Filter on case statement that is using measure

Received Response
62
Views
10
Comments
Rank 5 - Community Champion

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Answers

  • Rank 6 - Analytics Lead

      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'

  • Rank 6 - Analytics Lead

    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'

  • Rank 6 - Analytics Lead

    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 one 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'

  • Rank 6 - Analytics Lead

    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):

    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'

  • Rank 5 - Community Champion

    Thank you Jerry for helping me,

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

    pastedImage_0.png

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

    pastedImage_1.png

  • Rank 6 - Analytics Lead

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

    pastedImage_1.png

  • Rank 6 - Analytics Lead

    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

  • Rank 5 - Community Champion

    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.

    pastedImage_0.png

  • Rank 6 - Analytics Lead

    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'

  • Rank 5 - Community Champion

    Thanks Jerry

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

Welcome!

It looks like you're new here. Sign in or register to get started.