Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
anselmo_meira
Contributor II

including lastworkdate in a chart

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

3 Replies
MVP
MVP

Re: including lastworkdate in a chart

an idea

1.png


// 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;

anselmo_meira
Contributor II

Re: including lastworkdate in a chart

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.

anselmo_meira
Contributor II

Re: including lastworkdate in a chart

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?