Categories
Filter on case statement that is using measure

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
Answers
-
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'
0 -
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'
0 -
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'
0 -
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'
0 -
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
0 -
Does this box appear in your column formula?
Try checking that and see if it makes a difference.0 -
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
0 -
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.
0 -
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'
0 -
Thanks Jerry
Adding the SUM & BY to the case statement works - thanks and happy thanksgiving
0