topic Re: Calculated Dimension measuring between start date and no end date in QlikView

Calculated Dimension measuring between start date and no end date

Wed, 19 Sep 2012 12:21:16 GMT

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

rajni_batra — Wed, 19 Sep 2012 12:22:34 GMT

u can use today() if its none

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

jonathandienst — Wed, 19 Sep 2012 12:42:30 GMT

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

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

Wed, 19 Sep 2012 13:10:01 GMT

Thanks Jonathan!

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

Mon, 01 Oct 2012 15:42:21 GMT

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

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

jonathandienst — Tue, 02 Oct 2012 11:08:51 GMT

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