Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sona_sa
Creator II
Creator II

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
swuehl
MVP
MVP

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

))

View solution in original post

8 Replies
swuehl
MVP
MVP

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?

sona_sa
Creator II
Creator II
Author

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

swuehl
MVP
MVP

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

))

sona_sa
Creator II
Creator II
Author

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

sona_sa
Creator II
Creator II
Author

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

swuehl
MVP
MVP

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

sona_sa
Creator II
Creator II
Author

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

sona_sa
Creator II
Creator II
Author

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