Qlik Community

Qlik Community Help Discussions

Discussion board for questions on how to use Qlik Community and its features.

akumar_c
Contributor

Count of Employee

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Count of Employee

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

=sum(

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)

,1,0

))

8 Replies
MVP
MVP

Re: Count of Employee

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?

akumar_c
Contributor

Re: Count of Employee

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

Regards,

AKumar

MVP
MVP

Re: Count of Employee

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

=sum(

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)

,1,0

))

akumar_c
Contributor

Re: Count of Employee

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.

Regards,

AKumar

akumar_c
Contributor

Re: Count of Employee

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,

AKumar

MVP
MVP

Re: Count of Employee

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

akumar_c
Contributor

Count of Employee

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

akumar_c
Contributor

Re: Count of Employee

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 "\\172.16.11.211\Documents\OMS DASHBOARD.jpeg"

          objMail.attachments.add("\\172.16.11.211\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:\\172.16.11.211\Documents\test.jpeg'</BODY></HTML>"

            objMail.Send

          Set objMail = Nothing

          Set objOutlk = Nothing

    Msgbox " Mail delivered"

end sub