Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld online is next week! REGISTER 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
Partner

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
Partner

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.