Set Analysis to only show data greater than set value

[jlampard40]

Hi - I want to write set analysis which only pulls back values greater than, say, 7.  The problem I have is that the column of data I need to interrogate is a calculated dimension - as follows:

[HR Next Appraisal Date]-today(1)

This gives me the days until the next appraisal date for employees.  If I want to write an expression which basically says, count the number of records within the data who appraisal date is in the next 7 days, only for those employees who are on active assignment (field name = [HR Assignment Status]=Active Assignment), how do I write this?

I can write the set analysis for the expression to pull data for all active assignments, as follows:

=count({<[HR Assignment Status]={'Active Assignment'}>}[HR Assignment Number])

However, how do I incorporate my calculated dimension into this set analysis i.e. "[HR Next Appraisal Date]-today(1)" which will allow me to then ask it to pull only those records whose appraisal date is within the next 7 days.

I'm getting myself a bit muddled here guys - please help if you're able to.  Really appreciate it.  Rgds

[lironbaram]

hi try this

=count({<[HR Assignment Status]={'Active Assignment'},[HR Next Appraisal Date]={"<=$(=today()+7)"} >}[HR Assignment Number])

of course you can replace 7 with a variable so the user will be able to select the number of days 

[jlampard40]

Hi Liron - I tried the set analysis you suggested but it's not returning any data.  I know there IS data which should be returned.  In your formula it doesn't look like you've used the calculated dimension which is:

[HR Appraisal Date]-today(1)

This is giving me a column of data in my straight table of number of days until appraisal.  It's this I want to base my set analysis on - any further help would be much appreciated.  Thanks.