Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

5 Replies
rajni_batra
Specialist
Specialist

u can use today() if its none

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Thanks Jonathan!

Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein