Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kharry2415
New Contributor III

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

Re: Formula help: Project Plan start date

May be this

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

8 Replies

Re: Formula help: Project Plan start date

May be this

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

kharry2415
New Contributor III

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.

agigliotti
Honored Contributor II

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

May be this

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

kharry2415
New Contributor III

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.

kharry2415
New Contributor III

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)

kharry2415
New Contributor III

Re: Formula help: Project Plan start date

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

Community Browser