How to edit column formula to apply filter and save for reuse?

User_60BGL

I want to create a series of columns with filtering for my colleague's easy use.

I know that filters can be saved, but I would like to explore how to set the filter in a column using a formula, as this would be more convenient and understandable.

For example, for Legal Employer, I want to create a column for Active Legal Employer that displays Legal Employer name that Legal Employee Active Status = Active. I want to achieve this using a column formula and save it to a shared folder for reuse.

I tried using the FILTER function, but it requires at least one measure attribute, which doesn’t cover all cases.

Is there any way to achieve filtering using a column formula?

Many thanks.

Shankar-Oracle

Try the following and let me know, if this works for you:

First create an analysis with the below query. I saved this as LE_Filter:

SELECT
"Workforce Management - Worker Assignment Real Time"."Legal Employer"."Name",
"Workforce Management - Worker Assignment Real Time"."Legal Employer"."Status",
DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Legal Employer"."Status")
FROM "Workforce Management - Worker Assignment Real Time"
WHERE
(DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Legal Employer"."Status") = 'A')

This analysis contains a filter on legal employer status (for active). Save the analysis.

Create another analysis with the columns of your choice. Put a filter on "Legal Employer"."Name". On the filter dialog, for Operator choose "is based on results of another analysis". For "Saved Analysis" column, select LE_Filter, for Relationship column select "is equal to any", and for "Use values In Column" field "Legal Employer Name". Check the results. I tested this with an analysis with the following query:

SELECT "Workforce Management - Worker Assignment Real Time"."Legal Employer"."Legislation", "Workforce Management - Worker Assignment Real Time"."Legal Employer"."Name", "Workforce Management - Worker Assignment Real Time"."Legal Employer"."Status"FROM "Workforce Management - Worker Assignment Real Time"

After adding the filter, I see the following logical query in the "Advanced" tab of the respective analysis:
SELECT 0 s_0, "Workforce Management - Worker Assignment Real Time"."Legal Employer"."Legislation" s_1, "Workforce Management - Worker Assignment Real Time"."Legal Employer"."Name" s_2, "Workforce Management - Worker Assignment Real Time"."Legal Employer"."Status" s_3, DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Legal Employer"."Legislation") s_4, DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Legal Employer"."Status") s_5FROM "Workforce Management - Worker Assignment Real Time"WHERE("Legal Employer"."Name" IN ( SELECT saw_0 FROM (SELECT "Legal Employer"."Name" saw_0, "Legal Employer"."Status" saw_1, DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Legal Employer"."Status") saw_2 FROM "Workforce Management - Worker Assignment Real Time" WHERE DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Legal Employer"."Status") = 'A'
) nqw_1 ))

gclampitt

Or just a simple CASE statement

Case

when Legal Employee Active Status = 'Active'

then Legal Employer name

End

User_60BGL

@Shankar-Oracle

Thank you! I could follow the first step you provided, but I encountered some problem in step 2.

I created a new analysis and added Legal Employer Name and assignment count. However, I could not find the items in filter dialog e.g. operator, saved analysis, etc.

May you please show the screenshots or navigation? Also, I would like to ask if this filter will stay with the column, and can I save the column for reuse?

User_60BGL

@gclampitt

Thanks for your suggestion. I tried to add the column formula, but it returned error.

May you please share your column formula? Thanks.

gclampitt

See the Syntax error - you have a closing bracket but not an opening one

Shankar-Oracle

Please share the screen you are seeing, when you try to put a filter on "Legal Employer"."Name" in the second analysis

User_60BGL

@Shankar-Oracle

I tried to put a filter on Legal Employer Name via this path. May you please share your steps/navigation if I am wrong? Thanks a lot.

User_60BGL

@gclampitt

Thanks for your reply. 🙏

I have edited the column formula as below, however, the results shown nothing.

May you please advise if I am wrong? Thanks a lot.

gclampitt

Formula looks fine. Is your Legal Employer status definatley "A" ?
Add it into the query so can see what values it contains.

For me it is returning "Active"

so this formula works for me

case when "Legal Employer"."Status" = 'Active' then "Legal Employer"."Name" end

User_60BGL

@gclampitt

Thank you. It works!

I think I messed up the filter formula and CASE statement.