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

Formula help: Project Plan start date

Hello Qlik friends,

I need some help writing a formula within a KPI.

Our data source is a project management data base, and we want to know all of the charters that have past due start date.

We essentially want to count the number of charters that have a "Plan start date" before today. Can anyone help write this formula?

Here is what I currently have:

if(GetFieldSelections([PCI Type]='CHARTER'),

Count(if([Plan Start Date]<Today(1),0)))


Thanks!


Ken

1 Solution

Accepted Solutions
sunny_talwar

May be this

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

View solution in original post

8 Replies
sunny_talwar

May be this

Count(DISTINCT {<[Plan Start Date] = {"$(='<' & Date(Today()))"}>} Charter)

Anonymous
Not applicable
Author

Hello Sunny, thanks for the super quick reply.

Formula isn't quite working but I feel it can with a few tweaks. A few questions:

Is CHARTER supposed to be in ' '? I I feel it is missing punctuation. If it matters, the field 'CHARTER' is coming from is "PCI TYPE" Thanks for your help as always.

agigliotti
Partner - Champion
Partner - Champion

let's try with the below expression:

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

I hope it helps.

sunny_talwar

May be this

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

Anonymous
Not applicable
Author

Hi Andrea, thank you for the reply. Unfortunately that did not work, but Sunny's reply did! thank you for taking the time to reply.

Anonymous
Not applicable
Author

This worked! Thank you for your help as always

To test you even further, we want to see the value of these charters. "Value" is this formula:

Sum

(

{<

[Snapshot Type]={'LE'},

[LE Breakup]={'PLAN'},

Baseline=,

Baseline_LE=,

//AAKT-2772

//[Direct Indirect Flag]={$(=$(vDirectIndirect))},

[Default Type] = {$(=$(vCFType))},

//--

[SC Finance Filter]={1},

[Fiscal Year]={'$(=Year(Today()))'}

,[SC/Non-SC]={"$(=$(vSCNonSC))"}

>}

[Finance Amount] * [Probability Success %]

)

* $(vCurrencyConversion_CurrentYear)

+

Sum

(

{<

[Snapshot Type]={'LE'},

[LE Breakup]={'ACTUAL'},

Baseline=,

Baseline_LE=,

//AAKT-2772

//[Direct Indirect Flag]={$(=$(vDirectIndirect))},

[Default Type] = {$(=$(vCFType))},

//--

[SC Finance Filter]={1},

[Fiscal Year]={'$(=Year(Today()))'}

,[SC/Non-SC]={"$(=$(vSCNonSC))"}

>}

[Finance Amount]

)

* $(vCurrencyConversion_CurrentYear)

Where can we modify the formula you provided within this measure? Thanks in advance, you're the best!!!

sunny_talwar

May be add the same set analysis to this value expression

Sum({<[Snapshot Type] = {'LE'}, [LE Breakup] = {'PLAN'}, Baseline, Baseline_LE, [Default Type] = {$(=$(vCFType))}, [SC Finance Filter] = {1}, [Fiscal Year] = {'$(=Year(Today()))'}, [SC/Non-SC] = {"$(=$(vSCNonSC))"}, [Plan Start Date] = {"$(='<' & Date(Today()))"}, [PCI TYPE] = {'CHARTER'}>} [Finance Amount] * [Probability Success %]) * $(vCurrencyConversion_CurrentYear)

+

Sum({<[Snapshot Type] = {'LE'}, [LE Breakup] = {'ACTUAL'}, Baseline, Baseline_LE, [Default Type] = {$(=$(vCFType))}, [SC Finance Filter] = {1}, [Fiscal Year] = {'$(=Year(Today()))'}, [SC/Non-SC] = {"$(=$(vSCNonSC))"}, [Plan Start Date] = {"$(='<' & Date(Today()))"}, [PCI TYPE] = {'CHARTER'}>} [Finance Amount]) * $(vCurrencyConversion_CurrentYear)

Anonymous
Not applicable
Author

that worked!!! Needed to slightly modify the commas but this worked thank you