Calculate based on Zero value

[kpradeep5610]

Hi All,

Please find the below attached file which contains sample data.

Fields like

"Employ code", "current week salary" , "previous week salary" ,  "and variance"  these are all fields.

Requirement :

 "previous week salary"  = 0 and  "current week salary" <>0  -- count all employees(Joiners)

 "previous week salary"  <> 0 and  "current week salary" = 0  -- count all employees(Leavers).

Please help me to get the Qlik logic.

[tresesco]

Logically, it's like:

Joiners : Count (Distinct {<[Current week sal]-={0}, [Previous week sal]={0}>} EMPCODE)

Leavers : Count (Distinct {<[Current week sal]={0}, [Previous week sal]-={0}>} EMPCODE)

[kpradeep5610]

Hi, 

i'm really sorry, i have given wrong sample data. i apologize for that. 

please find the attached file, but requirement is same.

[Steven_Haught]

@kpradeep5610 

I would try creating the Previous week comp and Current Week comp in new fields. Then you can use the above formulas to do the count distinct. 

You could try adding a new section in the load and add the following: 

NewCompData:

Load

*
,If(EMPCODE=Previous(EMPCODE),Previous(Yearlysal)) as PrevComp
,If(EMPCODE=Previous(EMPCODE),Yearlysal) as CurrentComp

;

Load
*

RESIDENT YOURORIGINALTABLENAME

Order By EMPCODE,Calweek asc ;

DROP TABLE YOURORIGINALTABLENAME;

[ramchalla]

@kpradeep5610  can you please try the below code.

Test:

LOAD
EMPCODE,
Calweek,
Yearlysal

FROM [lib://dvSourceDataPath/ICO_Recharges_MI/Test/Sample data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Test1:
Load Distinct Calweek
Resident Test
Order By Calweek desc;

Let vCurrentCalWeek = Peek('Calweek',0,'Test1');
Let vPrevCalWeek = Peek('Calweek',1,'Test1');

Test12:

Load EMPCODE,
Calweek,
Yearlysal,
If((Calweek = $(vPrevCalWeek) and Yearlysal = 0), 'Joiner',
If((Calweek = $(vCurrentCalWeek) and Yearlysal = 0), 'Leaver') ) as Flag

Resident Test;

Drop Table Test, Test1;

Use these expressions in UI.

Count( {<Flag={"Joiner"}>} distinct EMPCODE)

Count( {<Flag={"Leaver"}>} distinct EMPCODE)

Thanks,

Ramesh.