I have a scenario as below,
need to select employee promotion details with respecto to period selection from Qlik sense filters (Year/Qtr/Month)
In the Sql It will looks like:
select Ename, Grade, PromotionDate from Emp_Fact
where (Eid, PromotionDate) in (Select Eid, max(PromotionDate) from Promotion_fact
where PromotionDate = (Qlik sense filter)
group by Eid)
So in Qlik sense, how to achieve this, and this need to show in 2 place as total count of promotion done for the period and list of employee name with Grade
Please help me on this scenario and let me know incase need any furhter details
Maybe something like this?
Load Ename, Grade, Max(PromotionDate) as PromotionDate Group by Ename, Grade ; Load Ename, Grade, PromotionDate from Emp_Fact where Eid = PromotionDate
Maybe you can show something about the result that you want to see. That would make it easier to get a solution.
I think you're looking for the FirstSortedValue function.
Use Ename as dimension and two measures: max(PromotionDate) and FirstSortedValue(Grade, -PromotionDate)
I'll rephrase my question, but I'm not interested in the visualizations, but what are the conditions that need to be met for the extraction? When are the right rows selected?
Its like, i am having complete history of data in DB table, lets take 10 yr data of emp promotion.(2010-2019) and other table are there as well. where i can not do any aggression in the script.
Requirement: need to represent, count of employee and their names, who are eligible for promotion.
Eligible promotion criteria : who are not promoted from past 3 yr.
By default its show eligible promotion for 2019, i.e. when no date filter selected. i.e. prediction for future
If i make any date selection, like: Year/Month/Quarter
then the count/list should show, who all were eligible for promotion during that selected period, as per the Eligible promotion criteria. i.e. past data analysis
Promotion for employee, will available consecutive years as well, i.e. employee would have got promotion back to back 2 year or alternate year, any combinations.
So i have to take the latest promotion date for the each employee and compare with the date selection from filter.
Please let me know, in case need more detail or having any confusion.