10 Replies Latest reply: Apr 10, 2018 4:16 AM by Vishweshwari Sunketa RSS

    Date help

    bhavani b

      Hi Experts,

       

      Can any one please help me on below requirement.

      From below table

      I need to count the IssueNo when Create > DueDate and [Item New String]='Authorised' and [Item Field] ='Staus' in KPi

      Please help me to write the expression.

       

      Thanks in advance.

        • Re: Date help
          shruthi B K

          if(Created > DueDate,count({<[Item New String]={'Authorised'},[Item Field] ={'Staus'}>}distinct IssueNo))

          • Re: Date help
            Youssef Belloum

            Hi,

             

            this should solve your problem:

             

            =Count({<IssueNo={"=Created>DueDate"},Item={'Authorized'},[Item Field] ={'Staus'}>}IssueNo)

              • Re: Date help
                bhavani b

                Its working Thank you

                  • Re: Date help
                    Youssef Belloum

                    Create a tableBox and put everything you need, go the LAYOUT section and put a CONDITION DISPLAY

                     

                    =GetSelectedCount(Dimension_field)


                    PS: if you click on a value on a table, it will select the Dimension(s) used on that table.

                    • Re: Date help
                      Juan Pedro Barroso Rodríguez

                      Hello:

                      In this case I prefer an alternative like calculate a Flag in a script.

                      I think that is more efficient. For example.

                            

                      ISSUENODUEDATECREATEDITEMNEWSTRINGITEMFIELDFLAG
                      68/1/1708/21/17AuthorisedStatus1
                      326/1/1706/21/17AuthorisedNo Status0
                      587/12/1706/22/17AuthorisedStatus0
                      597/13/1706/23/17AuthorisedStatus0
                      541/13/1812/24/17AuthorisedStatus0
                      592/8/1801/19/18AuthorisedStatus0
                      83/25/1804/14/18AuthorisedStatus1
                      24/1/1804/21/18AuthorisedStatus0
                      34/2/1804/22/18Not AuthorisedStatus0
                      44/3/1804/23/18Not AuthorisedStatus0
                      54/4/1804/24/18Not AuthorisedStatus0
                      64/5/1804/25/18Not AuthorisedStatus0

                       

                      LOAD

                           ISSUENO,

                           DUEDATE,

                           CREATED,

                           ITEMNEWSTRING,

                           ITEMFIELD,

                           IF( CREATE > DUEDATE AND ITEMNEWSTRING='Authorised' AND ITEMFIELD='Status', 1, 0)   AS FLAG

                      FROM ...;

                       

                      The KPI in this case is:

                       

                           sum(FLAG)

                       

                      Best Regards,

                      Juan P.

                  • Re: Date help
                    Vishweshwari Sunketa

                    Count(<{Create=">DueDate"},{[Item New String]='Authorised'},{[Item Field] ='Staus'}>IssueNo)

                    • Re: Date help
                      Prasad Borade

                      Hi,

                      Create flag as below

                      if(CREATED>DUEDATE,'YY','NN') as Flag

                       

                      and use below expression

                      =count(DISTINCT {<Flag={'YY'},ITEMNEWSTRING={'Authorised'},ITEMFIELD={'Status'}>}ISSUENO)