As on Date Employee

[#1]

Hi All,

I have got anodr Problem & need all your help.:-)

I have attached a Excel File where in i have got Employee name at any given point of time & have got star date & end date & sale amt.

If i select as on any date (as mentioned in the attached Excel file in second Tab "Required Output") i should get the output as given in the attached excel file "Second Sheet".

.

I should be ablt to get the name of the employee as on that day on a runtime if the Dates are not mentioned then i should get the blank but the amount should be get displayed in the report.

Need your suggestion to over come this problem.. 🙂

Rgds,

Srini

[#2]

The input data looks smthng like this..

Emp ID	Start Date	End Date	As on Date Slaes Peron Name	Sales
1000239	01-Jan-2011	31-Jan-2011	Srini	100
1000239	01-Feb-2011	28-Feb-2011	Manish	100
1000239	01-Mar-2011	31-Dec-4712	Sachin	100
1000239				200

As on date is a Auto generated Date or a Input Box Value "Date Paramter".

Note** the Last Row without a Sales Peron name, Start /date & End Date is not there in the Data.. Its a correction & m placing the new Excel file now..

rgds,

Srini

[#3]

Hi All,

I am able to achieve this by using a Variable

=aggr(max(if(START_DATE <= AsOnMonthEndDate,START_DATE)),EmpID)

here AsonMonthEndDate is a Autogenerated Date Filed & it gives me the Max Date as ondate which i select & use this Value in a Calculated Dimension as below

=if(START_DATE = Abov_Variable_Value,Emp Name)

But one more problem if in case i have EmpID;s where in i dont have any start Date & End Date but i want to display those records as well..

Any suggestions how to achieve that..wil b of gr8 Help

As i have mentioned in the above thread

Rgds,

Srini