Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
malradi88
Creator II
Creator II

Employment Rate Calculation

Dear Community,

I am working with the attached dataset and would like to calculate employment rate per 'Group Year' and in general. For the purposes of this expression, being 'employed' is if the person has had any salary (even one month): I tried the following expressions:

Per Group Year:

Count ({$<[Salary]= {">0"}, [Group Year] = {"#"}>} [Name]) / Count ({$<[Group Year]= {"#"}>} [Name])

In General:

Count ({$<[Salary]= {">0"}>} [Name]) / Count ([Name])

None of these worked. I tried other variations but no  luck as well. The idea is if I can calculate these, then I can make even moret master items relating to employment (employment per year, group number, gender etc...).

If you can think of anything please let me know! Your support is much appreciated!

Best,

Mohammed

15 Replies
Anonymous
Not applicable

Hi Mohammad,

you can try this

cross:
CrossTable (Date,Salary,16)
LOAD Name,
[Gender/ الجنس],
[تاريخ الميلاد/Date of Birth],
[الحاله الأجتماعيه/Marital Status],
[الموقف من التجنيد/Conscription Status],
[الأمراض المزمنه/Chronic Illness],
[Specialisation/التخصص],
[سنة التخرج/Graduation Date],
[الوظيفه السابقه/Past Occupation],
[سبب ترك العمل/Reason for Resignation],
[How Did the Applicant Hear about Us],
[Attended Training],
[Start Date],
[End Date],
[Group Year],
[Group Number],
[42005],
[42036],
[42064],
[42095],
[42125],
[42156],
[42186],
[42217],
[42248],
[42278],
[42309],
[42339],
[42370],
[42401],
[42430],
[42461],
[42491],
[42522],
[42552],
[42583],
[42614],
[42644],
[42675],
[42705],
[42736],
[42767],
[42795],
[42826],
[42856],
[42887],
[42917],
[42948],
[42979],
[43009],
[43040],
[43070]
FROM

(
ooxml, embedded labels, table is [المتدربات و مقدمي الرعاية ]);


load *, date(num#(Date) ,'MM-DD-YYYY')as Salary_Date Resident cross;

Drop table cross;

After this

sum(aggr(count({<Salary={">=0"}>}Name),Name)) will give you count.

malradi88
Creator II
Creator II
Author

Thank you Robin, I have done some tests and you are right, it appears that crosstable has 'deleted' all empty cells. I was going to just go back to the data set and replace empty cells with 0 but was wondering if you by any chance new the script function that could solve this (make Cross-table recognise empty cells)?

malradi88
Creator II
Creator II
Author

Would it be something like the highlighted seciton below? (using the APP you sent):

[المتدربات و مقدمي الرعاية ]:

CROSSTABLE ([Date],[Salary],17)

LOAD

....

....

....

....

....

....

....

....

FROM [lib://c/sample copy.xlsx]

(ooxml, embedded labels, table is [المتدربات و مقدمي الرعاية ]);

NullAsValue *;

SET NullValue = 0;

Temp:

CrossTable(Flag, Data, 71)

LOAD * FROM

FROM [lib://c/sample copy.xlsx]

(ooxml, embedded labels, table is [المتدربات و مقدمي الرعاية ]);

(qvd);



Anonymous
Not applicable

I just tried

NullAsValue '42*','43*';

Set NullValue =0;

It worked for the preceding load but I think not for the crosstable load... (I'm talking about QlikView)

Anonymous
Not applicable

You can solve it like that:

if(isnull([42005]),0,[42005]) as [42005] ,...

and so on...

I think NullAsValue doesn't work because QlikView/Sense seems to replace null() by a value at the end of the loading process...

Anonymous
Not applicable

I finally found the solution, the problem is the preceeding load, use resident load instead.

But I think, that is still not the solution you need?!?, because the cross table load creates a row for every year - month - name combination.

Have a look at the attachment...