Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
saumyashah90
Specialist
Specialist

Set analysis help Expression

AsOfDateEmployeeIDTeamCodeRegionDepartmentTeam
2020-05-311111012CSouthRetail ADigital A
2020-05-312222132VNorthRetail BDigital B
2020-06-302222472HEastRetail CDigital C
2020-06-303333475UEastRetail CDigital F
2020-06-304444413NWestRetail DDigital G
2020-07-312222763IEastRetail DDigital H
2020-07-315555472HEastRetail CDigital C
2020-07-304444472HEastRetail CDigital 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

Labels (3)
22 Replies
saumyashah90
Specialist
Specialist
Author

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 472H East Retail C Digital C

 

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

saumyashah90
Specialist
Specialist
Author

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 472H East Retail C Digital C

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)

saumyashah90
Specialist
Specialist
Author

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

saumyashah90
Specialist
Specialist
Author

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.

 

 

saumyashah90
Specialist
Specialist
Author

Any help people?

sunny_talwar

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

image.png

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), ',')
&')'

 image.png

saumyashah90
Specialist
Specialist
Author

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.

 

 

saumyashah90_1-1594961459467.png

 

sunny_talwar

Check now

image.png

saumyashah90
Specialist
Specialist
Author

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), ',')
&')'

saumyashah90
Specialist
Specialist
Author

Hi Sunny,

 

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

saumyashah90_0-1595931713972.png