Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
sunil-kumar5
Creator
Creator

Year and Month Filter

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.

 

4 Replies
fosuzuki
Partner
Partner

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

Great dreamer's dreams never fulfilled, they are always transcended.
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

Great dreamer's dreams never fulfilled, they are always transcended.
sunil-kumar5
Creator
Creator
Author

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.