Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
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

where's Salary?

Rows Q to AZ?

malradi88
Creator II
Creator II
Author

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

Anonymous
Not applicable

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

malradi88
Creator II
Creator II
Author

Sure. Please find attached. ! !

Anonymous
Not applicable

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
Creator II
Creator II
Author

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

khan_fayez
Creator
Creator

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
Creator II
Creator II
Author

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
Not applicable

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.