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

    Employment Rate Calculation

    Mohammed Al Radi

      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
          Robin Hausdörfer

          where's Salary?

          Rows Q to AZ?

          • Re: Employment Rate Calculation
            Robin Hausdörfer

            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;

              • Re: Employment Rate Calculation
                Mohammed Al Radi

                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
                    Robin Hausdörfer

                    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.

                     

                • Re: Employment Rate Calculation
                  Fayez Khan

                  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
                      Mohammed Al Radi

                      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
                      Fayez Khan

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

                      • Re: Employment Rate Calculation
                        Robin Hausdörfer

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