Year and Month Filter

[sunil-kumar5]

Hi All,

I have a employee master table with me which has two dates column Joining Date and Leaving date.

Now the issue is I have created a salary analysis dashboard but don't have salary release date column. Employee master table contains all the information of employees and data is only updated where there is now joiner.

I want Year and Month filter in my dashboard but don't know how to add it correctly.

First I used Joining date as year and month but when I selected filter it gave me the incorrect salary because it is fixed to joining date, if there isn't any person join in the month of August than it would not show in the filter, also it only show the data of new hires only which is obvious because year and month based on joining date.

Second I tried with master and temp calender but again base date is joining date and still issue is persisting.

Hope I would be able to clearfy, Please advise.

[fosuzuki]

Hi,

if I understood correctly, you need to have a Fact table with the monthly salary data. I believe that just having the Emp master table is not enough.

[PrashantSangle]

well it is typical SCD issue. You have to add one more column in you employee master as present_date which contain value of every month year from joining date to leaving date. For example

if your current table is 

emp_master:

Load * inline[

id, Joining_Date,Leaving_Date

1,1-Mar-2020,1-Sept-2020

];

then you have to add present_Date in such way that it create row for every month which lies between joining date and leaving date

like

Load * inline [

id, Joining_Date,Leaving_Date,Present_Date

1,1-Mar-2020,1-Sept-2020,1-Mar-2020

1,1-Mar-2020,1-Sept-2020,1-Apr-2020

1,1-Mar-2020,1-Sept-2020,1-May-2020

1,1-Mar-2020,1-Sept-2020,1-Jun-2020

1,1-Mar-2020,1-Sept-2020,1-Jul-2020

1,1-Mar-2020,1-Sept-2020,1-Aug-2020

1,1-Mar-2020,1-Sept-2020,1-Sept-2020

];

then using Present_Date, you can create Month & Year filter.

Regards,

Prashant Sangle

[PrashantSangle]

sample code like

Test:
Load id,Date(Date#(Joining_Date,'DD-MM-YYYY')) as Joining_Date, Date(Date#(Leaving_Date,'DD-MM-YYYY')) as Leaving_Date Inline[
id, Joining_Date,Leaving_Date
1,01-03-2020,01-09-2020
2,01-03-2010,01-09-2020
];

NoConcatenate
Final:
Load id,
Joining_Date,
Leaving_Date,
AddMonths(Joining_Date,IterNo()) as Present_Date
Resident Test
While MonthStart(Leaving_Date) >= MonthStart(AddMonths(Joining_Date,IterNo()))
;
Concatenate
Load *,
Joining_Date as Present_Date
Resident Test;

Drop Table Test;

Thanks & Regards,

Prashant Sangle

[sunil-kumar5]

Hi Prashant,

Just wanted to check on this "id, Joining_Date,Leaving_Date
1,01-03-2020,01-09-2020
2,01-03-2010,01-09-2020"

As this is the example you have taken but If I had three yours of data in that case DO I need to create the inline table that too long per your suggestion.