8 Replies Latest reply: Aug 7, 2018 8:36 AM by Ken Harry

# 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

• ###### Re: Formula help: Project Plan start date

May be this

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

• ###### Re: Formula help: Project Plan start date

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.

• ###### Re: Formula help: Project Plan start date

May be this

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

• ###### Re: Formula help: Project Plan start date

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!!!

• ###### Re: Formula help: Project Plan start date

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)

• ###### Re: Formula help: Project Plan start date

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

• ###### Re: Formula help: Project Plan start date

let's try with the below expression:

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

I hope it helps.

• ###### Re: Formula help: Project Plan start date

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.