topic Re: Set analysis help Expression in QlikView

Set analysis help Expression

saumyashah90 — Sat, 16 Nov 2024 18:22:03 GMT

AsOfDate	EmployeeID	TeamCode	Region	Department	Team
2020-05-31	1111	012C	South	Retail A	Digital A
2020-05-31	2222	132V	North	Retail B	Digital B
2020-06-30	2222	472H	East	Retail C	Digital C
2020-06-30	3333	475U	East	Retail C	Digital F
2020-06-30	4444	413N	West	Retail D	Digital G
2020-07-31	2222	763I	East	Retail D	Digital H
2020-07-31	5555	472H	East	Retail C	Digital C
2020-07-30	4444	472H	East	Retail C	Digital C

This is the employee data, i want create headcount analysis.

I cannot do anything in the script. All data will just get appended anymonth.

User will select 2 months for comparison and

They want get New to the company, Retained , Resigned from the company, Internal transfer

this should be valid based on selection of Region, Department and Team

So when i compare June and July Month i select only Team (Digital C ), I should get output

New to Bank: 1 (Employee 5555)

Resigned from Bank: 0

Internal Transfer: + 1 (Employee 4444)

Internal Transfer: -1(Employee 2222)

So when i compare June and July Month i select only Region (East) , I should get output

New to Bank: 1 (Employee 5555)
Resigned from Bank: 1 (Employee 3333)
Internal Transfer: +1 ( Employee 4444 )
Internal Transfer: -0

Note: Any two months can be selected for comparison, Jan Mar, Jan Nov, Feb Apr, June Nov, etc

Hint: Lowest grain is Team Code, If team code of employee is present and not present in last month , he is new to the bank,
if team code of employee is not present this month but present in last month, his has resigned

if team code is present for both month but different and it is same as filter selection, +ve internal transfer,

if team code is present for both month but different and it is not same as filter selection, -ve internal transfer

I have a composite key of EmployeeID,Teamcode at the backend if useful

Re: Set analysis help Expression

Saravanan_Desingh — Wed, 15 Jul 2020 02:59:22 GMT

Questions:

1111 - Why it is not been considered as Resigned?

2222 - TeamCode and Team are different. How you say it is +ve internal transfer

Re: Set analysis help Expression

saumyashah90 — Wed, 15 Jul 2020 03:32:42 GMT

Question ->1111 - Why it is not been considered as Resigned? :

Reply->

Because i have taken June July are comparing month.
At a time two months will be there for comparison.

If we would have compared May June or May July, 1111 would be resigned.

Question->2222 - TeamCode and Team are different. How you say it is +ve internal transfer

1st condition - he should not be totaly new to company, he/she should have some Teamcode tagged in previous month.

2nd condition - Team code should be different comparing two month

3rd condition - which ever teamcode and its related team is selected , if team code of current(higher month) is same its +ve transfer

Re: Set analysis help Expression

Saravanan_Desingh — Wed, 15 Jul 2020 13:39:50 GMT

I am still not clear.

2222:
2020-05-31 2222 132V North Retail B Digital B
2020-06-30 2222 472H East Retail C Digital C
2020-07-31 2222 763I West Retail D Digital H
4444:
2020-06-30 4444 413N West Retail D Digital G
2020-07-30 4444 445B West Retail D Digital E

With the above sample, please tell me where you are seeing as same team and which one is not?

Re: Set analysis help Expression

sunny_talwar — Wed, 15 Jul 2020 15:42:31 GMT

I am also not clear on Internal transfers, but the first two could be

='New to Bank: ' & Count(DISTINCT {<EmployeeID = P({<AsOfMonthYear = {"$(=Max({<Team>}AsOfMonthYear))"}, Team>})*e({<AsOfMonthYear = {"$(=Min({<Team>}AsOfMonthYear))"}, Team>}), Team>} EmployeeID) & '(' & Concat(DISTINCT {<EmployeeID = P({<AsOfMonthYear = {"$(=Max({<Team>}AsOfMonthYear))"}, Team>})*e({<AsOfMonthYear = {"$(=Min({<Team>}AsOfMonthYear))"}, Team>}), Team>} EmployeeID, ',') &')'

='Resigned: ' & Count(DISTINCT {<EmployeeID = e({<AsOfMonthYear = {"$(=Max({<Team>}AsOfMonthYear))"}, Team>})*p({<AsOfMonthYear = {"$(=Min({<Team>}AsOfMonthYear))"}, Team>}), Team>} EmployeeID) & '(' & Concat(DISTINCT {<EmployeeID = e({<AsOfMonthYear = {"$(=Max({<Team>}AsOfMonthYear))"}, Team>})*p({<AsOfMonthYear = {"$(=Min({<Team>}AsOfMonthYear))"}, Team>}), Team>} EmployeeID, ',') &')'

Re: Set analysis help Expression

Saravanan_Desingh — Wed, 15 Jul 2020 19:19:34 GMT

Resigned and New are captured in this code.

SET DateFormat='YYYY-MM-DD'; tab1: LOAD * INLINE [ AsOfDate, EmployeeID, TeamCode, Region, Department, Team 2020-05-31, 1111, 012C, South, Retail A, Digital A 2020-05-31, 2222, 132V, North, Retail B, Digital B 2020-06-30, 2222, 472H, East, Retail C, Digital C 2020-06-30, 3333, 475U, East, Retail C, Digital F 2020-06-30, 4444, 413N, West, Retail D, Digital G 2020-07-31, 2222, 763I, West, Retail D, Digital H 2020-07-31, 5555, 472H, North, Retail B, Digital C 2020-07-31, 4444, 445B, West, Retail D, Digital E ]; Left Join(tab1) LOAD Date(Max(AsOfDate)) As CurrMonth, Date(Max(AsOfDate,2)) As PrevMonth Resident tab1 ; Let vCurrMonth=Peek('CurrMonth'); Let vPrevMonth=Peek('PrevMonth'); Left Join(tab1) LOAD EmployeeID, Min(AsOfDate='$(vCurrMonth)') As F3, Min(AsOfDate='$(vPrevMonth)') As F4 Resident tab1 Where Match(AsOfDate,'$(vCurrMonth)','$(vPrevMonth)') Group By EmployeeID ; Left Join(tab1) LOAD *, If(F3<F4, 'New',If(F3>F4, 'Resigned')) As Status Resident tab1;

Re: Set analysis help Expression

Saravanan_Desingh — Wed, 15 Jul 2020 19:21:22 GMT

Output.

Re: Set analysis help Expression

Saravanan_Desingh — Wed, 15 Jul 2020 19:37:20 GMT

If I understand on the Internal Transfer logic,

SET DateFormat='YYYY-MM-DD'; tab1: LOAD * INLINE [ AsOfDate, EmployeeID, TeamCode, Region, Department, Team 2020-05-31, 1111, 012C, South, Retail A, Digital A 2020-05-31, 2222, 132V, North, Retail B, Digital B 2020-06-30, 2222, 472H, East, Retail C, Digital C 2020-06-30, 3333, 475U, East, Retail C, Digital F 2020-06-30, 4444, 413N, West, Retail D, Digital G 2020-07-31, 2222, 763I, West, Retail D, Digital H 2020-07-31, 5555, 472H, North, Retail B, Digital C 2020-07-31, 4444, 445B, West, Retail D, Digital E ]; Left Join(tab1) LOAD Date(Max(AsOfDate)) As CurrMonth, Date(Max(AsOfDate,2)) As PrevMonth Resident tab1 ; Let vCurrMonth=Peek('CurrMonth'); Let vPrevMonth=Peek('PrevMonth'); Left Join(tab1) LOAD EmployeeID, Count(DISTINCT Department) As F1, Min(AsOfDate='$(vCurrMonth)') As F3, Min(AsOfDate='$(vPrevMonth)') As F4 Resident tab1 Where Match(AsOfDate,'$(vCurrMonth)','$(vPrevMonth)') Group By EmployeeID ; Left Join(tab1) LOAD *, If(F3<F4, 'New',If(F3>F4, 'Resigned', If(F1>1 And AsOfDate='$(vCurrMonth)','+ve Internal Transfer','-ve Internal Transfer'))) As Status Resident tab1 Where Match(AsOfDate,'$(vCurrMonth)','$(vPrevMonth)');

Re: Set analysis help Expression

Saravanan_Desingh — Wed, 15 Jul 2020 19:49:42 GMT

A bug was fixed in this version.

SET DateFormat='YYYY-MM-DD'; tab1: LOAD * INLINE [ AsOfDate, EmployeeID, TeamCode, Region, Department, Team 2020-05-31, 1111, 012C, South, Retail A, Digital A 2020-05-31, 2222, 132V, North, Retail B, Digital B 2020-06-30, 2222, 472H, East, Retail C, Digital C 2020-06-30, 3333, 475U, East, Retail C, Digital F 2020-06-30, 4444, 413N, West, Retail D, Digital G 2020-07-31, 2222, 763I, West, Retail D, Digital H 2020-07-31, 5555, 472H, North, Retail B, Digital C 2020-07-31, 4444, 445B, West, Retail D, Digital E ]; Left Join(tab1) LOAD Date(Max(AsOfDate)) As CurrMonth, Date(Max(AsOfDate,2)) As PrevMonth Resident tab1 ; Let vCurrMonth=Peek('CurrMonth'); Let vPrevMonth=Peek('PrevMonth'); Left Join(tab1) LOAD EmployeeID, Count(DISTINCT Department) As F1, Min(AsOfDate='$(vCurrMonth)') As F3, Min(AsOfDate='$(vPrevMonth)') As F4 Resident tab1 Where Match(AsOfDate,'$(vCurrMonth)','$(vPrevMonth)') Group By EmployeeID ; Left Join(tab1) LOAD *, If(F3<F4, 'New',If(F3>F4, 'Resigned', If(AsOfDate='$(vCurrMonth)',If(F1>1,'+ve Internal Transfer','-ve Internal Transfer')))) As Status Resident tab1 Where Match(AsOfDate,'$(vCurrMonth)','$(vPrevMonth)');

Re: Set analysis help Expression

Saravanan_Desingh — Wed, 15 Jul 2020 19:50:47 GMT

Output.

Re: Set analysis help Expression

saumyashah90 — Thu, 16 Jul 2020 01:16:32 GMT

Let me change some value so that everyone is clear

It always depends on which two months you select for comparison.

Lets say we are selecting Jun and July for comparison and Higher month is July(2020-07-30)

so when there is a selection of Team Digital C

We should have 2222 as -ve internal transfer( No more in group C but still with the bank in other team)

and

We should have 4444 as +ve internal transfer( Was added in group C and was still present in bank with other team last/lower month)

I hope this would help

Re: Set analysis help Expression

saumyashah90 — Thu, 16 Jul 2020 01:16:59 GMT

Let me change some value so that everyone is clear
It always depends on which two months you select for comparison.
Lets say we are selecting Jun and July for comparison and Higher month is July(2020-07-30)
so when there is a selection of Team Digital C

We should have 2222 as -ve internal transfer( No more in group C but still with the bank in other team)
and
We should have 4444 as +ve internal transfer( Was added in group C and was still present in bank with other team last/lower month)

Re: Set analysis help Expression

saumyashah90 — Thu, 16 Jul 2020 01:18:54 GMT

I dont have flexibility to do any changes at the backend.

Data will get appended every month and month comparison will not always be current and previous.

It could be Jan April, Feb Mar, Mar Nov, etc

Re: Set analysis help Expression

saumyashah90 — Thu, 16 Jul 2020 01:29:33 GMT

Also i saw the application.

I am not getting correct resigned and new bank also?

Also i should have two months to select flexibility for the comparison.

Re: Set analysis help Expression

saumyashah90 — Thu, 16 Jul 2020 09:43:06 GMT

Any help people?

Re: Set analysis help Expression

sunny_talwar — Thu, 16 Jul 2020 11:25:13 GMT

I don't see any issues with New to Bank and Resigned once you select 2 months

I guess you intial input didn't match with the output you were expecting, but with the change of data it does... try out these two expression for Internal Transfers

='Internal Transfer: +' & Sum(DISTINCT {<EmployeeID = p({<AsOfMonthYear = {"$(=Max({<Team>}AsOfMonthYear))"}, Team>})*p({<AsOfMonthYear = {"$(=Min({<Team>}AsOfMonthYear))"}, Team>}), Team>} Aggr( If(Team = Only({<AsOfMonthYear = {"$(=Max({<Team>} AsOfMonthYear))"}>} Team), 1, 0), EmployeeID, Team)) & '(' & Concat(DISTINCT {<EmployeeID = p({<AsOfMonthYear = {"$(=Max({<Team>}AsOfMonthYear))"}, Team>})*p({<AsOfMonthYear = {"$(=Min({<Team>}AsOfMonthYear))"}, Team>}), Team>} Aggr( If(Team = Only({<AsOfMonthYear = {"$(=Max({<Team>} AsOfMonthYear))"}>} Team), EmployeeID), EmployeeID, Team), ',') &')'

='Internal Transfer: -' & Sum(DISTINCT {<EmployeeID = p({<AsOfMonthYear = {"$(=Max({<Team>}AsOfMonthYear))"}, Team>})*p({<AsOfMonthYear = {"$(=Min({<Team>}AsOfMonthYear))"}, Team>}), Team>} Aggr( If(Only({<EmployeeID = p({<AsOfMonthYear = {"$(=Max({<Team>}AsOfMonthYear))"}, Team>})*p({<AsOfMonthYear = {"$(=Min({<Team>}AsOfMonthYear))"}, Team>}), Team>} Team) = Only({<AsOfMonthYear = {"$(=Min({<Team>} AsOfMonthYear))"}>} Team), 1, 0), EmployeeID, Team)) & '(' & Concat(DISTINCT {<EmployeeID = p({<AsOfMonthYear = {"$(=Max({<Team>}AsOfMonthYear))"}, Team>})*p({<AsOfMonthYear = {"$(=Min({<Team>}AsOfMonthYear))"}, Team>}), Team>} Aggr( If(Team = Only({<AsOfMonthYear = {"$(=Min({<Team>} AsOfMonthYear))"}>} Team), EmployeeID), EmployeeID, Team), ',') &')'

Re: Set analysis help Expression

saumyashah90 — Fri, 17 Jul 2020 06:23:36 GMT

Hi Sunny,

When you click Digital C, we should not be getting 3333 in resigned.

Because 3333 was never a part of Digital C in June .

Your answer would be correct if there is no Digital C selected. Basically all should be based on selection.

Re: Set analysis help Expression

sunny_talwar — Fri, 17 Jul 2020 11:27:34 GMT

Check now

Re: Set analysis help Expression

saumyashah90 — Wed, 22 Jul 2020 01:33:47 GMT

Hi Sunny,

For internal transfer, when i keep sum part, i get value, but as soon as i add concat part(bold) nothing appears in my text box

='Internal Transfer: +' &
Sum(DISTINCT {<EmployeeID = p({<AsOfMonthYear = {"$(=Max({<Team>}AsOfMonthYear))"}, Team>})*p({<AsOfMonthYear = {"$(=Min({<Team>}AsOfMonthYear))"}, Team>}), Team>} Aggr(
If(Team = Only({<AsOfMonthYear = {"$(=Max({<Team>} AsOfMonthYear))"}>} Team), 1, 0), EmployeeID, Team))
& '(' &
Concat(DISTINCT {<EmployeeID = p({<AsOfMonthYear = {"$(=Max({<Team>}AsOfMonthYear))"}, Team>})*p({<AsOfMonthYear = {"$(=Min({<Team>}AsOfMonthYear))"}, Team>}), Team>} Aggr(
If(Team = Only({<AsOfMonthYear = {"$(=Max({<Team>} AsOfMonthYear))"}>} Team), EmployeeID), EmployeeID, Team), ',')
&')'

Re: Set analysis help Expression

saumyashah90 — Tue, 28 Jul 2020 10:22:07 GMT

Hi Sunny,

When i select Retail C only. I get 4444 in new to bank, where as it should be internal transfer.