Issue when subsequently calculating from formulas containing FILTER function

Martin Lowe

Background

• I have a single Oracle DB table set up as a basic Subject Area, comprising of one row per person per calendar month, showing data as of the last day of the calendar month (known as PERIOD_END). The table is called HCM_PERSON_SNAPSHOTS_DW.
• I have an Analysis that pulls out two separate months that will always be one year apart
• I have two formula fields that employ FILTER on the PERIOD_END to sum up an amount for the first and the last month of the overall data
  • FILTER("HCM_PERSON_SNAPSHOTS_DW"."CALCULATED_ANNUAL_SALARY" USING (cast("HCM_PERSON_SNAPSHOTS_DW"."PERIOD_END" as date) = @{var_EffDate}{date '2024-03-31'}))
  • FILTER("HCM_PERSON_SNAPSHOTS_DW"."CALCULATED_ANNUAL_SALARY" USING (cast("HCM_PERSON_SNAPSHOTS_DW"."PERIOD_END" as date) = timestampadd(SQL_TSI_month,-12,@{var_EffDate}{date '2024-03-31'})))

Problem

• both of the above formula fields correctly sum up employee salary for the filtered month, eg
  • now = 150,000
  • then = 100,000
• The issue is when I create a new formula field that is expressed as "now - then", which pasts in the individual formulas detailed above:
  • FILTER("HCM_PERSON_SNAPSHOTS_DW"."CALCULATED_ANNUAL_SALARY" USING (cast("HCM_PERSON_SNAPSHOTS_DW"."PERIOD_END" as date) = @{var_EffDate}{date '2024-03-31'}))
    -
    FILTER("HCM_PERSON_SNAPSHOTS_DW"."CALCULATED_ANNUAL_SALARY" USING (cast("HCM_PERSON_SNAPSHOTS_DW"."PERIOD_END" as date) = timestampadd(SQL_TSI_month,-12,@{var_EffDate}{date '2024-03-31'})))
• the resulting value should be 100,000, but it is not.

I feel that there must be a simple answer for this, but three of us are absolutely stumped. Can anyone help?

Raghavendra Raghunath-Oracle

Hi @Martin Lowe i dont see an issue in the formula, here is my version of it , using the HCM - Workforce Core Subject Area

Now - FILTER("HCM - Workforce Core"."Facts - Compensation"."Salary" USING "HCM - Workforce Core"."Time"."Date"= @parameter("var_EffDate")(date '2024-03-31'))

Then - FILTER("HCM - Workforce Core"."Facts - Compensation"."Salary" USING "HCM - Workforce Core"."Time"."Date"= TIMESTAMPADD(SQL_TSI_MONTH,-12,(@parameter("var_EffDate")(date '2024-03-31'))))

Diff - FILTER("HCM - Workforce Core"."Facts - Compensation"."Salary" USING "HCM - Workforce Core"."Time"."Date"= @parameter("var_EffDate")(date '2024-03-31'))-FILTER("HCM - Workforce Core"."Facts - Compensation"."Salary" USING "HCM - Workforce Core"."Time"."Date"= TIMESTAMPADD(SQL_TSI_MONTH,-12,(@parameter("var_EffDate")(date '2024-03-31'))))

Raghu