Set Analysis for fixed period and ignoring one filter

[MikeH1983]

Hi all. 

I'm having trouble writing a set analysis, hopefully someone out there can help!

I want an average headcount of Month End headcount values over a Selection time period. This here works:

Avg(Aggr(Count(
 {<MasterMonthEnd = {">=$(=[Selection Start Date])  <=$(=[Selection End Date])"}>} 
 if ( [Employment Status] <> 'Withdrawn'
    and [Action Start Date] <= MasterMonthEnd
    and [Action End Date] >= MasterMonthEnd
    and [Position Start Date] <= MasterMonthEnd
    and [Position End Date] >= MasterMonthEnd
     ,  [Personnel Number])
    )
, MasterMonthEnd,[Personnel Area]))

But now I want to ignore the selection of an Absence Type - i.e I want the values to stay the same if an Absence Type filter is selected. I've tried this below, but the values change when a filter is selected. 

Avg(Aggr(Count(
 {<MasterMonthEnd = {">=$(=[Selection Start Date])  <=$(=[Selection End Date])"}> * 1<[Absence Type]>} 
 if ( [Employment Status] <> 'Withdrawn'
    and [Action Start Date] <= MasterMonthEnd
    and [Action End Date] >= MasterMonthEnd
    and [Position Start Date] <= MasterMonthEnd
    and [Position End Date] >= MasterMonthEnd
     ,  [Personnel Number])
    )
, MasterMonthEnd,[Personnel Area]))

Any clues how I can set this expression to ignore an Absence Type filter? Thanks in advance

[mahaveerbiraj · Accepted solution]

Hi Mike,

Actually  we need to nullyfy that column(field) in Setanalysis,

Ex: Sum({< Product=  ><product= >} Amount)       </product= >

Please try below expression,

Avg(Aggr(Count(
 {<MasterMonthEnd = {">=$(=[Selection Start Date])  <=$(=[Selection End Date])"}>  ,[Absence Type]= >} 
 if ( [Employment Status] <> 'Withdrawn'
    and [Action Start Date] <= MasterMonthEnd
    and [Action End Date] >= MasterMonthEnd
    and [Position Start Date] <= MasterMonthEnd
    and [Position End Date] >= MasterMonthEnd
     ,  [Personnel Number])
    )
, MasterMonthEnd,[Personnel Area]))

[MikeH1983]

Thanks for the help so far.
Its still not working as intended. Perhaps we can work with a simpler example to make sure I understand the syntax?
In this case I want to count [Personnel Number]. I want the result to be unaffected by any filter applied to [Absence Type], but I do want it to change when other filters are selected.
I've tried the following code, but the result still is affected when I select to filter on Absence Type. What do you think?
Count (
{$<[Absence Type] = >}
distinct
[Personnel Number] )

[MikeH1983]

For Posterity, the correct syntax for my original problem is below. Note there is only one {<>} enclosing the entire set expression.

Avg(Aggr(Count(

                {<MasterMonthEnd = {">=$(=[Selection Start Date])  <=$(=[Selection End Date])"},[Absence Type]=>} 

                if ( [Employment Status] <> 'Withdrawn'

    and [Action Start Date] <= MasterMonthEnd

    and [Action End Date] >= MasterMonthEnd

    and [Position Start Date] <= MasterMonthEnd

    and [Position End Date] >= MasterMonthEnd

     ,  [Personnel Number])

    )

, MasterMonthEnd,[Personnel Area]))

[MikeH1983]

There was a problem with the solution syntax, but also a problem with my filter.

I was using a dimension on a Table to filter Absence Type, and that dimension was calculated. Changing it to a dimension that was simply the Absence Type field made the set expression recognise and ignore it, with the syntax {<[Absence Type]=>}. Thanks for your help!