Forum Discussion
Advocate IIHow to compare columns in a query (table) and a reference
I have a Headcount query (table) that contains a monthly snapshot of Employees.
The Headcount query (HDCT) has the following columns:
- HDCT Employee ID (ex. 6218)
- HDCT As of Date (ex. 06/30/2017)
- HDCT Salary Grade (ex. 6G)
- HDCT Current Record ID (ex. 6218_2017_06)
- HDCT Previous Record ID (ex. 6218_2016_05)
I created a Reference for the HDCT query (table) called HDCT_REF.
I have re-labeled the columns within HDCT_REF to be:
- HDCT_REF Employee ID
- HDCT_REF As of Date
- HDCT_REF Salary Grade
- HDCT_REF Current Record ID
- HDCT_REF Previous Record ID
I have created a relationship between HDCT and HDCT_REF using the following definition:
HDCT Previous Record ID = HDCT_REF Current Period ID
The cardinality is 1:1; the cross filter directions is Both
This relationship provides the means to view a monthly snapshot record and the associated previous monthly snapshot record on the same row.
As example, the HDCT 06/2017 row is joined to the HDCT_REF 05/2017 record using HDCT Previous Record ID (6218_2017_05) to HDCT_REF Current Record ID (6218_2017_05)
When creating a visualization using a Table, this data appears as expected.
I need to compare the HDCT Salary Grade value with the HDCT_REF Salary Grade value to identify any differences.
How can this be accomplished with a Calculation?
The solution is using the RELATED function in the creation of a new calculated column within the primary fact table (query) that references a column (field) from the related table (query).
In this instance, within the HDCT query a new calculated column was created:
HDCT Salary_Grade_Prev = RELATED(HDCT_REF[HDCT_REF(Salary Grade])
Because of the defined relationship between HDCT and HDCT_REF, HDCT Salary_Grade_Prev is able to reference the HDCT_REF Salary Grade value associated with the row from HDCT_REF.
With HDCT Salary_Grade_Prev defined, other new calculated columns can be created that reference it. For example,
HDCT Salary_Grade_Compare = IF(HDCT[HDCT Salary Grade]<>HDCT[HDCT Salary_Grade_Prev],"Salary Grade Change","")
1 Reply
mz180wThe solution is using the RELATED function in the creation of a new calculated column within the primary fact table (query) that references a column (field) from the related table (query).
In this instance, within the HDCT query a new calculated column was created:
HDCT Salary_Grade_Prev = RELATED(HDCT_REF[HDCT_REF(Salary Grade])
Because of the defined relationship between HDCT and HDCT_REF, HDCT Salary_Grade_Prev is able to reference the HDCT_REF Salary Grade value associated with the row from HDCT_REF.
With HDCT Salary_Grade_Prev defined, other new calculated columns can be created that reference it. For example,
HDCT Salary_Grade_Compare = IF(HDCT[HDCT Salary Grade]<>HDCT[HDCT Salary_Grade_Prev],"Salary Grade Change","")