topic Re: Previous month position calculation in App Development

Previous month position calculation

ajaykumar1 — Sat, 15 Aug 2020 07:46:02 GMT

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

Re: Previous month position calculation

rubenmarin — Sat, 15 Aug 2020 10:04:13 GMT

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

Re: Previous month position calculation

ajaykumar1 — Sat, 15 Aug 2020 15:26:50 GMT

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,

Re: Previous month position calculation

nirav_bhimani — Tue, 18 Aug 2020 05:57:45 GMT

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

Re: Previous month position calculation

ajaykumar1 — Thu, 24 Sep 2020 15:23:16 GMT

Thanks Nirav. It's worked with some changes.