Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
MikeH1983
Contributor III
Contributor III

Set Analysis for fixed period and ignoring one filter

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

 

Labels (2)
1 Solution

Accepted Solutions
mahaveerbiraj
Creator II
Creator II

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

View solution in original post

4 Replies
mahaveerbiraj
Creator II
Creator II

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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!