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