I'm having a Employee_Leave table consists of LEAVE_START_DATE and LEAVE_END DATE. I need to show the employee's leave count and remaining leaves. How can we show it in the dashboard? I'm planning to show with arbitrary date ranges.i.e., when the start date selected the employees on leave will be shown.
But having a doubt to implement this scenario. In the arbitrary date range only one date will be used as LEAVE_START_DATE and end date. Where i need to consider the LEAVE_END_DATE??How the two dates will be combined as one? and How can i show this scenario clearly to understand.Please anyone suggest me.Kindly find the sample app.
Maybe this will help: Canonical Date
What you need is to have another table where the leave and coming back date is related to each customer. For example, you'd have employee E in Table T1 that is associated to the table T2 by the employee's ID, this table having the fields LeaveDate and LeaveEndDate, as well as a filled called CanonicalDate.
When you use the dates in your UI, you'd use the CanonicalDate field.
You sample app contains no data, so it was difficult to understand what you are trying to achieve.
In this kind of situation where you want to query your data using arbitrary dates that may fall inside the date range of a leave record I would recommend you use a WHILE LOOP in your load statement to explode your data into single day/date records. That way at any date you can calculate how many day's leave each employee has taken and how many they have left.
If you can provide an example with data I could add an example WHILE loop.
Hi David / Sorin,
Thank you for valuable reply. Data is there.For some dates there is no values.Please check it by selecting the LEAVE_START_YEAR and LEAVE_END_YEAR.Please, guide me how to show the information clearly.
I this case you need to create a link table which combines both the date and finally link the combine date with calendar
try like this :
LEAVE_START_DATE as Date_Key
LEAVE_END DATE as Date_Key
LOAD Date as Date_Key,
NOTE:* Keep all the date format same.
*In the calendar using this new date i.e Date_key and count the leaves accordingly
I'm confused to implement this linked table.Because the both date fields are coming from same table.whether i need to concatenate them or not and in the Master calendar whether i need to use START_DATE or END_DATE??Please suggest me as per the below code.
ApplyMap('ABSENCE_REASON',ABS_ATTENDANCE_REASON_ID,NULL())as [LEAVE REASON],
ApplyMap('LEAVE_TYPES',ABSENCE_ATTENDANCE_TYPE_ID,NULL())as [LEAVE TYPE],
DATE( SICKNESS_START_DATE,'DD/MM/YYYY') as [SICKNESS START DATE],
DATE( SICKNESS_END_DATE,'DD/MM/YYYY') as [SICKNESS END DATE],
DATE_START as Date_Key
DATE_END as Date_Key
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
min(DATE_START) as minDate,
max(DATE_START) as maxDate
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
TempDate AS DATE_START,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Order By TempDate ASC;
Drop Table TempCalendar;