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

    Formula help: Project Plan start date

    Ken Harry

      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
          Sunny Talwar

          May be this

           

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

            • Re: Formula help: Project Plan start date
              Ken Harry

              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
                  Sunny Talwar

                  May be this

                   

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

                    • Re: Formula help: Project Plan start date
                      Ken Harry

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

                  • Re: Formula help: Project Plan start date
                    Andrea Gigliotti

                    let's try with the below expression:

                     

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

                     

                    I hope it helps.