Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
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).

4 Replies
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)

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.

Creator III

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.

NewCompData:

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

;

*

RESIDENT YOURORIGINALTABLENAME

Order By EMPCODE,Calweek asc ;

DROP TABLE YOURORIGINALTABLENAME;

Creator

Test:

EMPCODE,
Calweek,
Yearlysal

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

Test1:
Resident Test
Order By Calweek desc;

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

Test12:

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.

Tags
Community Browser