none
Filter() function not returning correct results RRS feed

  • Question

  • I am having trouble using two filter functions and some crossjoins.

    Here is the SQL I am trying to emulate:

    SELECT count(distinct PERSON_ID) --PERSON_JOB_F.*, PERSON_JOB_D., START.DATE, END.DATE --
    FROM PERSON_JOB_F, PERSON_JOB_D
    WHERE PERSON_JOB_F.PERSON_JOB_ID = PERSON_JOB_D.PERSON_JOB_ID
    AND STARTDATE  <= to_date('01-01-2008', 'MM-DD-YYYY') AND ENDDATE >= TO_DATE('01-01-2008', 'MM-DD-YYYY')
    AND ROLE != 'Intern' AND PERSON_STATUS = 'Active'

    This expression is to get a headcount. For some reason when I only use the first filter expression, i get 200 rows back. I would expect to get a lot more than this. Does anyone see anything that sticks out to them? I would appreciate any help! Thanks

    Aggregate(
    Filter(
            [Job History End Dt].[Hierarchy].[DT DT].Members ,
            [Job History End Dt].[Hierarchy].CurrentMember.MemberValue > CDate("1/1/2008")
        )
            *
    Filter(
            [Job History Strt Dt].[Hierarchy].[DT DT].Members ,
            [Job History Strt Dt].[Hierarchy].CurrentMember.MemberValue <= CDate("1/1/2008")
        )
            *
            {[Associate Job History].[Associate Status].&[Active] ,
            [Associate Job History].[Associate Status].&[Leave of Absence]}
            *
    Except(        {[Associate Job History].[Role].children},
            {[Associate Job History].[Role].&[Intern]}
        ),
            [Measures].[Row Count]
    )
    Monday, June 16, 2008 6:19 PM

All replies

  • Hi dcmander,

     

    please check on this:

     

    a) I don't see any Person dimension in your query. Why?

     

    b) You made 2 differences: > instead od >= and adding absence status besides active status.

     

    c) What is the aggregation function for your Row count measure (count, distinct count, sum, ...)?

     

    Additionally,

     

    d) Make another MDX to display only membervalues (for strt and/or end dimension, but separately in 2 MDX-es) as calc measure for all elements. Compare with expected. Then include filter on axis, not as a measure. Compare if it filters properly. Then check that for crossjoin of filters, also on axis. That should test your filtering and you'll know whether there is a problem. If you want, later you can transform what's on rows to count of that and put it as a new calc measure on columns.

     

    Personally, I think you're getting number of periods (actualy, combination of periods with certain statuses and roles) instead of number of persons. There can be more persons that started on the same date (first filter) so you're getting less than expected.

     

     

    Tomislav Piasevoli

    Business Intelligence specialist

    http://www.softpro.hr

     

     

    Monday, June 16, 2008 8:39 PM
    Answerer