Oracle Transactional Business Intelligence

Products Banner

Issue when subsequently calculating from formulas containing FILTER function

Received Response
21
Views
1
Comments

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?

Tagged:

Answers

  • 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