Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Re: Calculated Dimension measuring between start date and no end date

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
5 Replies
rajni_batra
Valued Contributor

Re: Calculated Dimension measuring between start date and no end date

u can use today() if its none

MVP
MVP

Re: Calculated Dimension measuring between start date and no end date

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

Re: Calculated Dimension measuring between start date and no end date

Thanks Jonathan!

Not applicable

Re: Calculated Dimension measuring between start date and no end date

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

MVP
MVP

Re: Calculated Dimension measuring between start date and no end date

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
Community Browser