Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
kpradeep5610
Contributor III
Contributor III

Calculate based on Zero value

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.

4 Replies
tresesco
MVP
MVP

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
Contributor III
Contributor III
Author

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
Creator III
Creator III

@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
Creator
Creator

@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.