Forum Discussion

LeahGJ's avatar
LeahGJ
Frequent Visitor
4 years ago

Filter with multiple conditions

Hi,

I have a listing of employees salary payments for 2022. I only want to filter employees who have been paid contract gratuity in the corresponing period, including all other allowance fields. An example of the data is below.

 

Simply, I want the results to return employee #109500 with values for cycle_no. 1 only, since in cycle_no. 1, pay_year 2022,  the employee was paid both contract graturty and another allowance. Note that 'Element Name' is the allowance field.  

 

EMPLOYEE_NUMBERPAYROLL_CLASSPAY_YEARCYCLE_NO.CLASSIFICATION_NAMEELEMENT_NAMEPAY_VALUE
109500 Calendar Month20221EarningsBasic Salary Monthly22848
109500 Calendar Month20221EarningsCONTRACT GRATUITY 06682252.8
109500 Calendar Month20222EarningsBasic Salary Monthly22848
379002 Calendar Month20221EarningsBasic Salary Monthly22848
379002 Calendar Month20222EarningsBasic Salary Monthly22848
169090 Calendar Month20221EarningsBasic Salary Monthly22848
169090 Calendar Month20222EarningsBasic Salary Monthly22848
Need Help

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    You need to do this via a measure* as it is a "filtering up"  pattern.

     

    First find the employees that have contract gratuity.  Then find all the payment data for these employees.

     

    * technically a calculated column would also work since these facts are immutable.

    • LeahGJ's avatar
      LeahGJ
      Frequent Visitor
      4 years ago

      Thanks Ibendlin. can you share a meaure for this calculated column please. 

      • lbendlin's avatar
        lbendlin
        Super User
        4 years ago

        please indicate the expected outcome based on the sample data you provided.