Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
petter
Partner - Champion III
Partner - Champion III


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].



View solution in original post

4 Replies
petter
Partner - Champion III
Partner - Champion III


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].



Anonymous
Not applicable
Author

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!

petter
Partner - Champion III
Partner - Champion III

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

steps1989
Contributor II
Contributor II

Kindly Take a look on this link

Qlik Sense script error