Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there -
I went through few discussions on this title but nothing helped hence creating a new one.
I have data like this -
Emp Id | Start Date | End Date |
---|---|---|
I want an output like this -
Month-Year | Count of Employees |
---|---|
*** I have start dates from 1992 but I have to build a report of employee count from Jan-2015 (Which will consider the count before 2015 as well).
Thanks in advance
-Sneh
Hi,
You will have to use the interval match function.
Regards,
Kaushik Solanki
Use Loops in the Script (while loop) to create a single date field from a date range or you can use an IntervalMatch to do this.
First you need to make it as single date
Load EmpId, Date(StartDate + IterNo() - 1) as FinalDate
While Date(StartDate + IterNo() - 1) <= EndDate;
Load EmpId, StartDate, EndDate;
Then create Straight table like
Date(FinalDate,'MMM-YYYY') as Calculated Dimension and label called MonthYear
Expression is Count({<Date = {">=$(=MakeDate(2015, 01, 01)) <=$(=Max(Date))"}>}EmpId)
Hi Thanks for the quick response .
Below is the script I've written -
Employee:
Load *,
if(not exists(Endtemp), today(), Endtemp) as [End Date]
;
LOAD
//'Employee' as Table_Name,
Upper(REGION) as REGION,
ANALYST_KEY,
LOCAL_ID,
ANALYST_NAME,
START_DATE as [Start Date],
Month(START_DATE) as Start_Month,
Year(START_DATE) as Start_Year,
END_DATE as Endtemp,
Month(END_DATE) as End_Month,
Year(END_DATE) as End_Year
FROM
ANALYST.qvd
(qvd);
Calendar:
Load
Date,
Year(Date) & '-' & Month(Date) as Yyyymm,
Month(Date) as Month,
Year(Date) as Year;
Load
Date(MinDate+IterNo()) as Date
while MaxDate - MinDate >= IterNo()
;
LOAD
MIN([Start Date]) as MinDate,
Max([End Date]) as MaxDate
Resident Employee;
Interval:
Inner join (Employee)
intervalMatch (Date)
Load distinct [Start Date], [End Date]
resident Employee
;
In UI table I'm ng Yyyymm as dimension and Count(Distinct Analyst_Key) as Expression.
and the numbers don't seem to match with the actuals numbers.
Where m I wrong?
Snehasis Patnaik Jun 19, 2017 6:00 AM (in response to Sunny Talwar )
Hi Thanks for the quick response .
Below is the script I've written -
Employee:
Load *,
if(not exists(Endtemp), today(), Endtemp) as [End Date]
;
LOAD
//'Employee' as Table_Name,
Upper(REGION) as REGION,
ANALYST_KEY,
LOCAL_ID,
ANALYST_NAME,
START_DATE as [Start Date],
Month(START_DATE) as Start_Month,
Year(START_DATE) as Start_Year,
END_DATE as Endtemp,
Month(END_DATE) as End_Month,
Year(END_DATE) as End_Year
FROM
ANALYST.qvd
(qvd);
Calendar:
Load
Date,
Year(Date) & '-' & Month(Date) as Yyyymm,
Month(Date) as Month,
Year(Date) as Year;
Load
Date(MinDate+IterNo()) as Date
while MaxDate - MinDate >= IterNo()
;
LOAD
MIN([Start Date]) as MinDate,
Max([End Date]) as MaxDate
Resident Employee;
Interval:
Inner join (Employee)
intervalMatch (Date)
Load distinct [Start Date], [End Date]
resident Employee
;
In UI table I'm ng Yyyymm as dimension and Count(Distinct Analyst_Key) as Expression.
and the numbers don't seem to match with the actuals numbers.
Where m I wrong?
Don't see anything wrong in the script... may be share some sample data with the expected output and we can try running your script to check what might not be working here.
Dear Snehasis,
It may help you.
//SET DateFormat='DD.MM.YYYY';
EmpData:
Load * INLINE [
EmpID, Leave, Date Start, Date End
100, Leave, 01.01.2017, 10.01.2017
100, Leave, 15.01.2017, 20.01.2017
101, Leave, 10.01.2017, 20.01.2017
102, Leave, 18.01.2017, 30.01.2017
103, Leave, 25.01.2017, 31.01.2017
105, Leave, 30.01.2017, 31.01.2017
];
Final:
LOAD [EmpID], [Leave], [Date Start] as Sdate , [Date End] as Edate,
Date([Date Start]+iterno()-1) as Date
resident EmpData while [Date Start]+IterNo()-1 <=[Date End];
drop table EmpData;
Thanks ,
Arvind Patil