Previous month position calculation

[ajaykumar1]

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 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

TIA

Aj

[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]

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 · Accepted solution]

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.

Regards,

Nirav

[ajaykumar1]

Thanks Nirav. It's worked with some changes.