Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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.
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?