Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
(
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.
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)?
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);
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)
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...
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...