To show the movement between Levels for the chosen FY

[VIrajMP]

Hi All,

I have a requirement in which I need to track the movement of employees between Levels (Level 1 Level2) for the chosen financial year. Below are the cases for which I need to calculate the no of employees.

1) No change : No change in Levels between selected FY and Previous FY 

2) Level1 -> Level2 and Level2 -> Level1 : Level changes between selected FY and Previous FY

The sample data is given below.

EmpCode	FY	Level
1	3/31/2019	Level1
1	3/31/2020	Level1
1	3/31/2021	Level1
2	3/31/2019	Level1
2	3/31/2020	Level1
2	3/31/2021	Level2
3	3/31/2019	Level2
3	3/31/2020	Level2
3	3/31/2021	Level2
4	3/31/2019	Level1
4	3/31/2020	Level1
4	3/31/2021	Level2
5	3/31/2019	Level1
5	3/31/2020	Level2
5	3/31/2021	Level1

Desired Results

Selected FY : 03/31/2021

Movement	No of Employees
No Change	2
Level1 -> Level2	2
Level2 -> Level1	1

Selected FY : 03/31/2020

Movement	No of Employees
No Change	4
Level1 -> Level2	1
Level2 -> Level1	0

Will appreciate if anyone can help me out on this requirement.

Thanks

Viraj Phadnis

India

[VIrajMP]

Thanks a lot, exactly as I wanted.

[Syndicate_Admin · Accepted solution]

Puede hacerlo con tres columnas calculadas

No Change = 
var c = Moves[FY]
var p = edate(c,-12)
var ct = SELECTCOLUMNS(filter(Moves,Moves[FY]=c),"e",[EmpCode],"l",[Level])
var pt = SELECTCOLUMNS(filter(Moves,Moves[FY]=p),"e",[EmpCode],"l",[Level])
return countrows(INTERSECT(ct,pt))

1 to 2 = 
var c = Moves[FY]
var p = edate(c,-12)
var ct = SELECTCOLUMNS(filter(Moves,Moves[FY]=c && Moves[Level]="Level2"),"e",[EmpCode])
var pt = SELECTCOLUMNS(filter(Moves,Moves[FY]=p && Moves[Level]="Level1"),"e",[EmpCode])
return countrows(INTERSECT(ct,pt))

2 to 1 = 
var c = Moves[FY]
var p = edate(c,-12)
var ct = SELECTCOLUMNS(filter(Moves,Moves[FY]=c && Moves[Level]="Level1"),"e",[EmpCode])
var pt = SELECTCOLUMNS(filter(Moves,Moves[FY]=p && Moves[Level]="Level2"),"e",[EmpCode])
return countrows(INTERSECT(ct,pt))

Y el resultado se vería así:

o si desea poner los valores en filas