Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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]))
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]))
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]))
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!