Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this
Count({<[Plan Start Date] = {"$(='<' & Date(Today()))"}, [PCI TYPE] = {'CHARTER'}>} [PCI TYPE])
May be this
Count(DISTINCT {<[Plan Start Date] = {"$(='<' & Date(Today()))"}>} Charter)
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.
let's try with the below expression:
Sum( {< [PCI Type] = {'CHARTER'}, [Plan Start Date] = {"$(='<' & Date(Today()))"} >} 1 )
I hope it helps.
May be this
Count({<[Plan Start Date] = {"$(='<' & Date(Today()))"}, [PCI TYPE] = {'CHARTER'}>} [PCI TYPE])
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.
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!!!
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)
that worked!!! Needed to slightly modify the commas but this worked thank you