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

User_60BGL · Nov 13, 2024 2:25AM

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 · Nov 13, 2024 2:59AM

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 · Nov 13, 2024 6:16AM

Or just a simple CASE statement

Case

when Legal Employee Active Status = 'Active'

then Legal Employer name

End

User_60BGL · Nov 15, 2024 9:42AM

@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?