Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ajaykumar1
Creator III
Creator III

Previous month position calculation

Hi Everyone,

I have to calculate 3 KPI's in qlik sense.

I have to show Count of resouce Id's.if i Select 20-06-2020 as example. 

1)Need to show count of resource id's who changed the department and came into HR.

     E.g : 101 and 108 are changed from Technical to HR, so need to show count as 2

2) Mainly I need help here :Need to show who left the department HR.-----Count as 2(90 and 95 are changed from HR to TAG)

3)Total count of resource id's belong to HR department.---4

Sample Data:

I have the sample data like below.

Refresh TimeResource IDDepartmentMonth
04-04-202090HRApr
04-04-202095HRApr
01-05-2020101SalesMay
01-05-2020103SalesMay
08-06-2019105TechnicalJune
10-06-2019108TechnicalJune
15-06-2020110HRJune
20-06-2020101HRJune
20-06-2020108HRJune
20-06-2020115HRJune
20-06-202090TAGJune
20-06-202095TAGJune

 

TIA

Aj

1 Solution

Accepted Solutions
nirav_bhimani
Partner - Specialist
Partner - Specialist

Dear Ajay,

Please find the attached QVF file with Logic,   You are looking for Transfer In & Transfer Out Cases Only. Below is the reference screenshot.

nirav_bhimani_0-1597730166333.png

 

Regards,

Nirav

 

 

 

View solution in original post

4 Replies
rubenmarin

Hi, you can get help from script flagging department switch from/to HR:

OrigData:
LOAD * INLINE [
    Refresh Time, Resource ID, Department, Month
    04-04-2020, 90, HR, Apr
    04-04-2020, 95, HR, Apr
    01-05-2020, 101, Sales, May
    01-05-2020, 103, Sales, May
    08-06-2019, 105, Technical, June
    10-06-2019, 108, Technical, June
    15-06-2020, 110, HR, June
    20-06-2020, 101, HR, June
    20-06-2020, 108, HR, June
    20-06-2020, 115, HR, June
    20-06-2020, 90, TAG, June
    20-06-2020, 95, TAG, June
];

ProcessedData:
LOAD
	[Refresh Time], 
	[Resource ID], 
	Department, 
	Month,
	If(Peek([Resource ID])=[Resource ID] and Peek(Department)='HR', 1, 0) as hasLeftHR,
	If(Peek([Resource ID])=[Resource ID] and Department='HR', 1, 0) as hasComeHR
Resident
	OrigData
Order By
	[Resource ID], [Refresh Time]
;

DROP Table OrigData;

 

So for 1) you can count or filter on flag hasComeHR, for 2) is the asme but with hasLeftHR flag.

For 3) you can use: =Sum(Aggr(If([Refresh Time]=Max(TOTAL <[Resource ID]> [Refresh Time]) and Department='HR',1), [Resource ID],[Refresh Time]))

ajaykumar1
Creator III
Creator III
Author

Thanks Ruben for your help.

I tried the below expressions in Real data but getting zero values.I will validate data and get back for make it as solved.

1)count({<hasLeftHR={'1'},Refresh Time={"$(=Max(Refresh Time))"}>}distinct Resource ID)

2)count({<hasComeHR={'1'},Refresh Time={"$(=Max(Refresh Time))"}>}distinct Resource ID)

 

Thanks,

Aj

 

nirav_bhimani
Partner - Specialist
Partner - Specialist

Dear Ajay,

Please find the attached QVF file with Logic,   You are looking for Transfer In & Transfer Out Cases Only. Below is the reference screenshot.

nirav_bhimani_0-1597730166333.png

 

Regards,

Nirav

 

 

 

ajaykumar1
Creator III
Creator III
Author

Thanks Nirav. It's worked with some changes.