Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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].
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].
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!
Happy to help Please mark the question as answered by tagging it as correct...
Kindly Take a look on this link