8 Replies Latest reply: Feb 2, 2012 1:26 PM by iTree Consulting RSS

    Count of Employee

    iTree Consulting

      Hi Friends,


      I am attaching a qvw file. Where I have to calculate the Grade wise count of employee between median salary and 44th Salary.


      Please ingore the 0 salary.


      Thanks for help

        • Re: Count of Employee
          Stefan Wühl

          Create a straight table chart with dimension PaySclTyp_Text (your Grade) and as expression:

          =count(distinct  if([Annual Sal_ANSALARY]>=F_Salary and [Annual Sal_ANSALARY]<=M_Salary, Employee_Code))


          Just because I am curious:

          What is a 44th Salary?

            • Re: Count of Employee
              iTree Consulting

              Thanks Friend...


              We have tried this but the figures are not correct...so please check the current attached QVW and excel for  expected values...

              And 44th salary is the 44th percentile salary of that grade....




                • Re: Count of Employee
                  Stefan Wühl

                  Maybe like attached?


                  I think your variables you defined won't take the dimension values into account, so I needed to calculate the percentiles inside the advanced aggregation like



                  if( [Annual Sal_ANSALARY] <= aggr( fractile(total<PaySclTyp_Text> [Annual Sal_ANSALARY] ,0.5 ), PaySclTyp_Text,Employee_Code)

                  and [Annual Sal_ANSALARY] >= aggr( fractile(total<PaySclTyp_Text> [Annual Sal_ANSALARY],0.44 ), PaySclTyp_Text,Employee_Code)



                    • Re: Count of Employee
                      iTree Consulting

                      Thanks Dear,


                      This one is the correct answer and I want the same. If any things required I will contact you on the same discussion.


                      Thanks Again.




                        • Re: Count of Employee
                          iTree Consulting

                          Hi Friend,


                          The provided solution was correct for 44th Count, Suppose I want same for 66th and 75th, For that what we need to change in expression. Can you help me on this.


                          For reference please attached qvw file.


                          Thanks & Regards,


                            • Re: Count of Employee
                              Stefan Wühl

                              This part of the expression is the upper bound, the median or 50th percentile:

                              fractile(total<PaySclTyp_Text> [Annual Sal_ANSALARY] ,0.50.)


                              and this second fractile function is the lower bound, the 44th percentile:

                              fractile(total<PaySclTyp_Text> [Annual Sal_ANSALARY] ,0.44 )


                              So just adapt those two numbers in bold as you need (take care of the upper and lower bound, if you e.g. use median and 66th percentage. Then you need to put 0.66 into the first fractile and 0.50 into the second. First value always larger equal than the second value).

                                • Count of Employee
                                  iTree Consulting

                                  Awesone Friend, Got u'r feedback and Thanks for It. This is the one of the best solution in this discussion.

                                    • Re: Count of Employee
                                      iTree Consulting

                                      Thanks for last solution.



                                      Hi Can u help me on this ?



                                      Hi Friends,


                                      I want to send clipboard image by Outlook Box with any attachment. It should to go as an image in mail body. For this I am using the below macro. Please check it.


                                      But my image is not going properly in mail body. I am able to send by attachment but not in mailbody.


                                      sub mSendMail

                                                Dim objOutlk 'Outlook

                                                Dim objMail 'Email item

                                                Dim strMsg

                                                dim strMailTo

                                                dim sString

                                                dim oldFormat

                                                Dim Rng

                                                Const olMailItem = 0

                                                Const CdoReferenceTypeName = 1

                                                Set objOutlk = CreateObject("Outlook.Application")

                                                Set objMail = objOutlk.createitem(olMailItem)

                                                Set Rng = Nothing

                                                objMail.To = "amit.kumar@teamcomputers.com"

                                                objMail.subject = "OMS DASHBOARD"

                                                ActiveDocument.ActiveSheet.CopyBitmapToClipboard true

                                                ActiveDocument.Sheets("Dashboard-email").ExportBitmapToFile "\\\Documents\OMS DASHBOARD.jpeg"

                                                objMail.attachments.add("\\\Documents\OMS DASHBOARD.jpeg")

                                                strMsg = "This is an automatic generated email from QlikView for OMS DASHBOARD."

                                                objMail.body = strMsg

                                          objMail.HTMLBODY = "<HTML><BODY><img align=baseline border=0 hspace=0 src='cid:\\\Documents\test.jpeg'</BODY></HTML>"


                                                Set objMail = Nothing

                                                Set objOutlk = Nothing

                                          Msgbox " Mail delivered"

                                      end sub