topic Re: Set Analysis to only show data greater than set value in QlikView

Set Analysis to only show data greater than set value

jlampard40 — Fri, 12 Oct 2018 06:34:14 GMT

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

Re: Set Analysis to only show data greater than set value

lironbaram — Fri, 12 Oct 2018 06:53:36 GMT

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

Re: Set Analysis to only show data greater than set value

jlampard40 — Fri, 12 Oct 2018 07:09:50 GMT

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.