Solved: Re: Count of Employee - Qlik Community

sona_sa · ‎2012-02-02

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

swuehl · ‎2012-02-02

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

swuehl · ‎2012-02-02

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 · ‎2012-02-02

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 · ‎2012-02-02

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 · ‎2012-02-02

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 · ‎2012-02-02

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 · ‎2012-02-02

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 · ‎2012-02-02

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

sona_sa · ‎2012-02-02

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