15 Replies Latest reply: Jul 4, 2017 3:15 PM by Robin Hausdörfer

# 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

• ###### Re: Employment Rate Calculation

where's Salary?

Rows Q to AZ?

• ###### Re: Employment Rate Calculation

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

• ###### Re: Employment Rate Calculation

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

• ###### Re: Employment Rate Calculation

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)
[Name],
[Gender/ الجنس],
[تاريخ الميلاد/Date of Birth],
[الحاله الأجتماعيه/Marital Status],
[الموقف من التجنيد/Conscription Status],
[الأمراض المزمنه/Chronic Illness],
[Specialisation/التخصص],
[الوظيفه السابقه/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];

[Name],
[Gender/ الجنس],
Date([تاريخ الميلاد/Date of Birth] ) AS [تاريخ الميلاد/Date of Birth],
[الحاله الأجتماعيه/Marital Status],
[الموقف من التجنيد/Conscription Status],
[الأمراض المزمنه/Chronic Illness],
[Specialisation/التخصص],
[الوظيفه السابقه/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;

• ###### Re: Employment Rate Calculation

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

• ###### Re: Employment Rate Calculation

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.

• ###### Re: Employment Rate Calculation

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)?

• ###### Re: Employment Rate Calculation

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

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

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

....

....

....

....

....

....

....

....

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

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

NullAsValue *;

SET NullValue = 0;

Temp:

CrossTable(Flag, Data, 71)

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

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

(qvd);

• ###### Re: Employment Rate Calculation

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)

• ###### Re: Employment Rate Calculation

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

• ###### Re: Employment Rate Calculation

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

• ###### Re: Employment Rate Calculation

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

• ###### Re: Employment Rate Calculation

you can try this

cross:
CrossTable (Date,Salary,16)
[Gender/ الجنس],
[تاريخ الميلاد/Date of Birth],
[الحاله الأجتماعيه/Marital Status],
[الموقف من التجنيد/Conscription Status],
[الأمراض المزمنه/Chronic Illness],
[Specialisation/التخصص],
[الوظيفه السابقه/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
[C:\Users\o652956\Desktop\sample copy.xlsx]
(
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.