topic including lastworkdate in a chart in QlikView

including lastworkdate in a chart

Anonymous — Wed, 13 Jan 2016 19:51:17 GMT

Hi all,

I am creating a chart based on a date and number of working days. What I would like to do is to show the initial date and all the subsequent days in a chart. Let's see and example: a person started to work on 15/10/2015 and worked for 5 days. If we use lastworkday function, it will return 21/10/2015. So, basically, I would like to see in a chart the days 15,16,19,20,21 which one person in each. The chart will have as Y axis for person and X axis for date.

Does anyone have an idea? Thanks

Re: including lastworkdate in a chart

maxgro — Wed, 13 Jan 2016 21:32:39 GMT

an idea

// some test data

AA:

load *, LastWorkDate(startdate, days) as enddate inline [

person, startdate, days

A, 01/01/2016, 5

B, 02/01/2016, 6

C, 03/01/2016, 7

D, 15/10/2015, 5

];

// make all dates

Left Join (AA)

load

person,

startdate,

date(startdate + IterNo() -1) as date

Resident AA

While startdate + iterno() -1 <= enddate;

// add flag for mon...fri

Left join (AA)

load

person, date,

if(not match(num(WeekDay(date)), 5,6),1) as flagworking

Resident AA;

Re: including lastworkdate in a chart

Anonymous — Thu, 14 Jan 2016 10:41:26 GMT

Thanks maxgro!

I've made an expression in a chart because the number of worked days can be editable and unfortunately it takes longer than i expect to show the results.

So here is the function I've used:

count( distinct if(([Start Work Date]<=WORK_DAY and LastWorkDate([Start Work Date], Round(([NofDays]/vNofDays),1))>=WORK_DAY), Person))

vNofDays is a variable which divides the number of worked days

Now I am finding a way to put this part in LOAD script: LastWorkDate([Start Work Date], Round(([NofDays]/vNofDays),1)

and leaving vNofDays as a inputfield.

Re: including lastworkdate in a chart

Anonymous — Thu, 14 Jan 2016 19:02:07 GMT

In addition to the problem, I found what is delaying calculation. This is the original function " count( distinct if(([Start Work Date]<=WORK_DAY and LastWorkDate([Start Work Date], Round(([NofDays]/vNofDays),1))>=WORK_DAY), Person)) "

and if I replace LastWorkDate([Start Work Date], Round(([NofDays]/vNofDays),1)), by another field not editable ( [End Work Date]) the problem still occurs.

So, If Statement is delaying to display chat.

Does anyone have another way of doing that?