Variable help..

[#1]

Hi everyone,

I need help with a variable...

I have attached a sample.

here is what i need to achieve.

1. I need a variable which contains Public holiday dates

2. i need a variable that contains employee sickness / holiday / training dates.

3. then i need to add these 2 varibables to a variable that calculates 'Days in Range' which is then used to work out averages.

Ok, i have completed point 1 (public holidays variable)

i dont know how to create the 2nd variable. i need it to contain the following 'Diary Event Type codes'..

          01, (holiday)

          02, (Sickness)

          03, (Other)

          13, (Field Day ISS) - Training.

I have a variable 'vDaysinRange' which works out the working day range.

So i want to add in the vBankHolidays and the new variable from point 2 (both to be excluded) into 'vDaysinRange'

can anyone help please?

[Gysbert_Wassenaar]

PublicHolidays:

LOAD

//  concat(chr(39)&date(HolidayDate)&chr(39),',') AS PublicHol

   concat(distinct num(HolidayDate),',') AS PublicHol

FROM comm75724.xls

(biff, embedded labels, table is [BankHol$]);

//create variable containing public holiday dates 

LET vBankHolidays = peek('PublicHol',0,'PublicHolidays');

AbsentDays:

load concat(distinct AbsentDay, ',') as AbsentDays;

load DiaryEventDateFrom + iterno() -1 as AbsentDay

while DiaryEventDateFrom + iterno() -1 <= DiaryEventDateTo;

Load DiaryEventDateFrom, DiaryEventDateTo from

comm75724.xls

(biff, embedded labels, table is [DiaryEvent$])

where match(DiaryEventTypeCode,'01','02','03','13');

LET vAbsentDays = peek('AbsentDays');

SET vAbsentDays2 = $(vBankHolidays),$(vAbsentDays);

SET vDaysinRange = networkdays(vMinDate,vMaxDate,$(vAbsentDays2));

edit: highlighted the where clause for DiaryEventTypeCode)

edit 2: highlighted the changes to the PublicHolidays load

[#3]

Hi Gysbert,

thanks for your reply.

In your example, the vAbsentDays only need to include dates where the

'Diary Event Type code' is either 01,02,03,13. that is where i am stuck at.

Is it possible for you to put it into my sample? its easier for me to understand that way if its not too much trouble

[#4]

hi Gysbert , yes i see that now, sorry, i completely over looked it!

i cant seem to get the DATE function to work on the new field in your example. the vAbsent Days variable is displaying a mixture of formats.

Can you point me in the right direction to format this field please?

Thanks

[#5]

Hi again..

This doesnt seem to be working...

This month for example has 21 working days in it (thats if no employees have no time off) but looking at the new sample i have attached, every employee has 21 days against them.

employee code 201  has had 4 days off sick this month so the daysin range for them should be 17 not 21.

Is the variable EXCLUDING the dates in the vAbsentDays2 variable?

thanks

[Gysbert_Wassenaar]

Ah, you need it per employee. Different approach then with a very simple flag field. See attached qvw.

edit: SET vDaysinRange = networkdays(vMinDate,vMaxDate,$(vBankHolidays)) - count({<DiaryEventTypeCode={'01','02','03','13'}>} total <DiaryEventEmployeeCode> DiaryEventEmployeeCode);

[#7]

Thats great, sorry i should have said that in the beginning..

One other question though...

Is there a way to get the solution in a variable?

The application this needs to go in is already in use and uses the variable 'DaysInRange' which currently only works out working days. They want it changed to deduct Bank Holidays and Absence too, so i would like to just update the variable otherwise it would mean changing the set analysis in loads of expressions across the whole app..

Is this possible?

Thanks for your help..