5 Replies Latest reply: Oct 2, 2012 7:08 AM by Jonathan Dienst RSS

    Calculated Dimension measuring between start date and no end date

      I'm struggling with creating a calculated demension to measure the running time between two date fields.

       

      Recieved Date

      Sent Date

       

      I want to display the running count in days when the Sent Date is 'None'

       

      This is what I was working with:

       

      =if(NetWorkDays(WP_Consult_Table.CreationDate,WP_Consult_Table.DateSent = 'None')=1,'Same Day',

        if(NetWorkDays(WP_Consult_Table.CreationDate,WP_Consult_Table.DateSent = 'None')=2,'1 Day',

                          if(NetWorkDays(WP_Consult_Table.CreationDate,WP_Consult_Table.DateSent = 'None')=3,'2 Day',

                                    if(NetWorkDays(WP_Consult_Table.CreationDate,WP_Consult_Table.DateSent= 'None')=4,'3 Day',

                                              if(NetWorkDays(WP_Consult_Table.CreationDate,WP_Consult_Table.DateSent= 'None')=5,'4 Day',

                                                        if(NetWorkDays(WP_Consult_Table.CreationDate,WP_Consult_Table.DateSent= 'None')=6,'5 Day','Over 5 Days'))))))

        • Re: Calculated Dimension measuring between start date and no end date
          Jonathan Dienst

          Hi

           

          Networkdays takes 2 dates as parameters, you are passing a date and a boolean (0/1) so this expression is not going to give you the results you expect. Which date should be the second date when the sent date is none?

           

          Perhaps this meets your requirements:

           

          If(WP_Consult_Table.DateSent = 'None',
                    If(NetWorkDays(WP_Consult_Table.CreationDate, Today()) < 6,
                              Pick(NetWorkDays(WP_Consult_Table.CreationDate, Today()), 'Same Day', '1 Day', '2 Day', '3 Day', '4 Day', '5 Day'),
                              'Over 5 Days'
                    ),
            
                    If(NetWorkDays(WP_Consult_Table.CreationDate, WP_Consult_Table.DateSent) < 6,
                              Pick(NetWorkDays(WP_Consult_Table.CreationDate, WP_Consult_Table.DateSent), 'Same Day', '1 Day', '2 Day', '3 Day', '4 Day', '5 Day'),
                              'Over 5 Days'
                    )
          )
          

           

          But I would stronly advise that you put this in script rather than a calculated dimension.

           

          Hope that helps

          Jonathan