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?
