topic Re: Set Analysis for fixed period and ignoring one filter in App Development

Set Analysis for fixed period and ignoring one filter

MikeH1983 — Thu, 23 Dec 2021 18:28:44 GMT

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

Re: Set Analysis for fixed period and ignoring one filter

mahaveerbiraj — Tue, 06 Aug 2019 10:27:09 GMT

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]))

Re: Set Analysis for fixed period and ignoring one filter

MikeH1983 — Wed, 07 Aug 2019 08:07:07 GMT

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] )

Re: Set Analysis for fixed period and ignoring one filter

MikeH1983 — Wed, 07 Aug 2019 08:58:38 GMT

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]))

Re: Set Analysis for fixed period and ignoring one filter

MikeH1983 — Wed, 07 Aug 2019 08:59:49 GMT

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!