Employment Rate Calculation

[malradi88]

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

[Anonymous]

where's Salary?

Rows Q to AZ?

[malradi88]

Yes Q-AZ! Apologies didn't mention I un-pivoted those columns.

[Anonymous]

could you provide the unpivoted version or your script ...

[malradi88]

Sure. Please find attached. ! !

[Anonymous]

found 2 problems but it still doesn't work I think:

1)

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

2) Salary has lost its numerical representation due to crosstable LOAD.

You can solve it like that, but be aware that you have to do it for each numerical field:

TEMP:
CROSSTABLE ([Date],[Salary],17)
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],
[42491], [42005], [42036], [42064], [42095], [42125], [42156], [42186], [42217], [42248],
[42278], [42309], [42339], [42370], [42401], [42430], [42461], [42522], [42552], [42583],
[42614], [42644], [42675], [42705], [42736], [42767], [42795], [42826], [42856], [42887],
[42917], [42948], [42979], [43009], [43040], [43070];

LOAD
[Name],
[Gender/ الجنس],
Date([تاريخ الميلاد/Date of Birth] ) AS [تاريخ الميلاد/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],
Date(Date#([End Date], 'DD/MM/YYYY') ) AS [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 [sample copy.xlsx]
(ooxml, embedded labels, table is [المتدربات و مقدمي الرعاية ]);


LOAD num(num#(Salary)) as Salary_num, * Resident TEMP;
DROP Table TEMP;
drop field Salary;
RENAME Field Salary_num to Salary;

[malradi88]

Thank you Robin! How do you know if salary lost it's numerical representation? I am working with a slightly different data set (had to remove actual names and other sensitive information from the one I sent over) and when I place the salary field onto a sheet the numbers appear.

Yes I tried putting actual years instead of {"*"} but still got no result

Best,

Mohammed

[Anonymous]

Hi,

There are 2 approaches to it. One is to sum all the salary fields and check if the sum(salary)>0. But then this approach will not be dynamic.

2nd approach is to pivot the salary fields and then sum it up grouping by ID. If the sum > 0, then apply the condition

[malradi88]

Thank you Fayez,

I applied the first approach in the original app and the sum(salary) is  >0. Assuming salary was being read in numeric form in the sample qvf attached above - would you have any suggestions re the 'employment rate' expressions I mentioned? Perhaps there is another way to order the expressions that would produce a result.

Best,

Mohammed

[Anonymous]

I think the crosstable LOAD "deleted" all your empty cells.

Try replacing the empty cells for salary by 0 or change your script!

Crosstable will ignore null() values. --> makes sense

----------

hm, tried it before in QlikView --> problem as mentioned.

But Sense seems to work. The problem is here, that

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

is the same as (for your sample data)

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

Hve a look at the attachment.