Count if start date of project is within the next 3 months

[Emiliaa]

Hi.

I am looking to create a master measure for a KPI that yields the number of projects that will start within today's given date and the next three months (~90 days) from that. I have tried a bunch of different solutions but have not been able to get it to work. 

I use the fields [start date] which is in date format and [project name] .

I hope someone in here can help me out. 

[hic]

You can use a set expression like

[project name]={"=Max([start date])>=Today() and Max([start date])<=Today()+90"}

(The Max() function is there just in case the data model allows multiple start dates for each project.)

[Emiliaa]

I'm quite new to qlik, can you explain how to use a set expression? 

Because if I just copy your expression into the function field, I get an error message. Additionally, the set expression fields in the right panel are greyed out. 

[hic · Accepted solution]

It depends on what you want.

If you want a filter pane that just shows the relevant projects, you can use the following expression instead of a reference to [project name]:

=Aggr(Only({<[project name]={"=Max([start date])>=Today() and Max([start date])<=Today()+90"}>} [project name]),[project name])

Or if you have a data model where each project is linked to a number of people or a budget amount, you can use one of the following as a measure in a chart:

Count({<[project name]={"=Max([start date])>=Today() and Max([start date])<=Today()+90"}>} PersonID )
Sum({<[project name]={"=Max([start date])>=Today() and Max([start date])<=Today()+90"}>} BudgetAmount )

[Emiliaa]

Thank you! This worked.