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

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

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

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'

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

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'

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