topic Re: last working days in QlikView

last working days

Fri, 23 Oct 2015 13:02:21 GMT

if(len([Date Closed])<=0,now(),[Date Closed]) as DT2

Instead of Now() i want to find last working days of [date closed] month.

the date format is- 'mm/dd/yy hh:mi am/pm'

Re: last working days

Gysbert_Wassenaar — Fri, 23 Oct 2015 14:06:33 GMT

Is max([Date Closed]) what you're looking for?

Re: last working days

sunny_talwar — Fri, 23 Oct 2015 14:12:30 GMT

May be this:

=WeekStart(MonthEnd([Date Closed]), 5)

Re: last working days

jonathandienst — Fri, 23 Oct 2015 14:15:01 GMT

Last working day of the month:

If(Len([Date Closed]) <= 0, FirstWorkDate(MonthEnd([Date Closed]), 1), [Date Closed]) as DT2

Re: last working days

swuehl — Fri, 23 Oct 2015 14:24:06 GMT

Isn't [Date Closed] unknown for those records where the condition is true?

Which Month / Last Working Date do you want to use then?

Maybe

FirstWorkDate(MonthEnd(Today()), 1)

Re: last working days

jonathandienst — Fri, 23 Oct 2015 14:29:43 GMT

Too true

Maybe this instead

If(Len([Date Closed]) = 0, FirstWorkDate(MonthEnd(Today()), 1), [Date Closed]) as DT2

Re: last working days

Fri, 23 Oct 2015 16:54:29 GMT

I followed your suggestion and the date is converted into number and I have tried vice versa but it didn't('mm/dd/yy hh:mi am/pm').

Re: last working days

sunny_talwar — Fri, 23 Oct 2015 17:04:36 GMT

May be try this:

Date(If(Len([Date Closed]) = 0, FirstWorkDate(MonthEnd(Today()), 1), [Date Closed]), 'MM/DD/YY hh:mm TT') as DT2

Re: last working days

Fri, 23 Oct 2015 17:16:02 GMT

Almost reaching.

But-

If we have august data then it shpould come 31st of aug and sep -30th sep.

But it is showing oct -30th

may be we have used today() but inplace of today() if we use Date Closed then it is giving us different result.

Re: last working days

sunny_talwar — Fri, 23 Oct 2015 17:28:11 GMT

The problem with using [Date Closed] is that you are saying that if Len([Date Closed]) = 0 (meaning it is empty) then do the first thing which give you null() or 0 as well.

Have you tried this:

Date(WeekStart(MonthEnd([Date Closed]), 5), 'MM/DD/YY hh:mm TT') as DT2

Re: last working days

Anonymous — Fri, 23 Oct 2015 17:41:17 GMT

You can use like this.

Date(If(len([Date Closed]<0,today()-1,[Date Closed],'MM/DD/YY hh:mm TT') AS DT2

this will fill the null value with previous day and rest will have Date closed.

Regards,

Imran K

Re: last working days

Sat, 24 Oct 2015 02:09:50 GMT

It is showing november date-2nd nov

Re: last working days

sunny_talwar — Sat, 24 Oct 2015 11:25:47 GMT

Right, that make sense. Finally try this:

Date(FirstWorkDate(MonthEnd([Closed Date]), 1), 'MM/DD/YY hh:mm TT') as DT2

Re: last working days

Sat, 24 Oct 2015 11:42:39 GMT

Thanks for helping me. Apprecite it.

No its not helped me.

Re: last working days

swuehl — Sat, 24 Oct 2015 12:32:26 GMT

No its not helped me.

I believe that's because we are just guessing around. If you want help, you need to detail on

- where we are starting from:

Post some sample lines of your data records, e.g. an Excel table, INLINE script code or a small sample QVW.

- where we are heading to:

Describe your requirements, i.e. the expected results, in more detail.

Could be your data records, after transformation, attached to your post e.g. as Excel table.

I currently think we are missing both pieces of the puzzle.

Re: last working days

Sun, 25 Oct 2015 03:09:52 GMT

I have attached the QVW file. Please help.

Re: last working days

Sun, 25 Oct 2015 03:10:38 GMT

I have attached the file.Please help.

Re: last working days

Anonymous — Sun, 25 Oct 2015 09:05:18 GMT

Hi Sesa,

Please elaborate on your requirement more.

As per your information it looks like,data for max month

in source Oct/Nov is shown.

Re: last working days

Sun, 25 Oct 2015 09:33:20 GMT

Hi, maybe try this:

if(len([Date Closed])<=0,

Date(Floor(MonthEnd([Date Closed]),1)-if(WeekDay(MonthEnd([Date Closed]))>4,WeekDay(MonthEnd([Date Closed]))-4,0),'MM/DD/YY hh:mm TT'),[Date Closed]) as DT2

Re: last working days

swuehl — Sun, 25 Oct 2015 13:31:34 GMT

Could you upload your input data table (the Excel file)?

It looks like the OPEN status records don't show [Date Closed] values in your input records, which is reasonable.

In your original post, you said:

"Instead of Now() i want to find last working days of [date closed] month."

Since the [date closed] is not set for those records, which month do you want to show then?

If I understood what you are going to do (calculating a duration between open and close, excluding weekends, holidays and non working hours) Now() seems to be reasonable for me to (without actually knowing your requirements ... see my last post why it's important to tell us more if you want help from the community).

You should be able to calculate the last working date of a month with above expressions, if needed.