Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

hahnky21
New Contributor II

Count values based on Plan Start Date Range

Hello! 

I'd greatly appreciate some help writing a formula within a KPI. My question is very similar to this one: https://community.qlik.com/message/1528073#1528073

Our data source is a project management data base, and we want to know the count of charters that have Plan Start dates within the next 60 days (i.e. Charters that will be converting to Projects within the next 60 days).

How can I adjust the below formula to capture a count of charters with Project Plan Dates greater than Today, but less than 60 days from Today?


Count({<[Plan Start Date] = {"$(='<' & Date(Today()))"}, [PCI Type] = {'CHARTER'}>} [PCI Type])

Thanks so much!
Kyle

1 Solution

Accepted Solutions
MVP
MVP

Re: Count values based on Plan Start Date Range


Count( { 1 <[Plan Start Date] = {">$(=Today(1))<$(=Date(Today(1)+60))"}, [PCI Type] = {'CHARTER'}>} [Charter ID])


Notice that you will probably want to count the unique charters and I presume that you have some sort of ID for each charter. I have just named that field [Charter ID].


If you count like you suggested you will always get 1 as a count as you are counting the [PCI Type] and you filter out only one [PCI Type].



4 Replies
MVP
MVP

Re: Count values based on Plan Start Date Range


Count( { 1 <[Plan Start Date] = {">$(=Today(1))<$(=Date(Today(1)+60))"}, [PCI Type] = {'CHARTER'}>} [Charter ID])


Notice that you will probably want to count the unique charters and I presume that you have some sort of ID for each charter. I have just named that field [Charter ID].


If you count like you suggested you will always get 1 as a count as you are counting the [PCI Type] and you filter out only one [PCI Type].



hahnky21
New Contributor II

Re: Count values based on Plan Start Date Range

Thanks so much Petter! Your suggestion was nearly perfect - the only adjustment I had to make to get exactly what I  was looking for was removing the 1 early in the expression. Here is my final result:

Count({ < [Plan Start Date] = {">$(=Today(1))<$(=Date(Today(1)+60))"}, [PCI Type] = {'CHARTER'}>} [PCI Type])

Thanks again!

MVP
MVP

Re: Count values based on Plan Start Date Range

Happy to help Please mark the question as answered by tagging it as correct...

steps1989
New Contributor

Re: Count values based on Plan Start Date Range

Kindly Take a look on this link

Qlik Sense script error

Community Browser