Calculated Dimension measuring between start date and no end date

[#1]

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

[rajni_batra]

u can use today() if its none

[jonathandienst · Accepted solution]

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

[#4]

Thanks Jonathan!

[#5]

Hi Jonathan,

How would I use the same expression but calculate time in hours?

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

[jonathandienst]

Rob

I dont understand your question. Perhaps you should make an attempt and I could assist you. But please raise this in a new post. This one has been marked as answered, and you will get a better response in a new post. But please elaborate some on your requirement.

Regards

Jonathan