Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
cheryl
Contributor III
Contributor III

Weighted average in bar char based on the selected dimension

HI Community,

I am trying to show the weighted average hourly rate (weighted by actual hours) in Bar chart in Qliksense. Below is my expression:

 

sum(aggr((Sum({<[p.Project Category]={'Customer work'}>*<[p.State.Name]={'Active','Completed'}>}[ActHours.Duration.value.Hours])/Sum({<[p.Project Category]={'Customer work'}>*<[p.State.Name]={'Active','Completed'}>} total <Period> [ActHours.Duration.value.Hours])*[p.CommercialHREUR]),Period,ProjectID))

I allow users to select the dimensions that they are interested in. For example (Period + Debtor region) selection should show the weighted hourly rate for each debtor region per period.

cheryl_1-1683128983441.png

However above expression doesn't work, it only works when the selection is Period + Period.

What I want to achieve is that the denominator will be the sum of hours based on the selected dimension.

I found out that if I select the Period + p.Program.Name as dimensions, and change the expression to :

 

sum(aggr((Sum({<[p.Project Category]={'Customer work'}>*<[p.State.Name]={'Active','Completed'}>}[ActHours.Duration.value.Hours])/Sum({<[p.Project Category]={'Customer work'}>*<[p.State.Name]={'Active','Completed'}>} total <Period,p.Program.Name> [ActHours.Duration.value.Hours])*[p.CommercialHREUR]),Period,p.Program.Name, ProjectID))

 

It will work. But I want them to also work based on whichever dimension the user selected.

 

Any ideas?

 

Thanks a lot,

Cheryl

 

Labels (5)
2 Solutions

Accepted Solutions
Kushal_Chawda

@cheryl  look at the getobjectfield() function. You can try below

sum(aggr((Sum({<[p.Project Category]={'Customer work'}>*<[p.State.Name]={'Active','Completed'}>}[ActHours.Duration.value.Hours])/Sum({<[p.Project Category]={'Customer work'}>*<[p.State.Name]={'Active','Completed'}>} total <Period,p.Program.Name> [ActHours.Duration.value.Hours])*[p.CommercialHREUR]),$(=GetObjectField(0)), $(=GetObjectField(1)), ProjectID))

View solution in original post

cheryl
Contributor III
Contributor III
Author

Thanks Kushal,

 

I have changed the expression a bit and now it works!

sum(aggr((Sum({<[p.Project Category]={'Customer work'}>*<[p.State.Name]={'Active','Completed'}>}[ActHours.Duration.value.Hours])/Sum({<[p.Project Category]={'Customer work'}>*<[p.State.Name]={'Active','Completed'}>} total <$(=GetObjectField(0)), $(=GetObjectField(1))> [ActHours.Duration.value.Hours])*[p.CommercialHREUR]),$(=GetObjectField(0)), $(=GetObjectField(1)), ProjectID))

View solution in original post

4 Replies
Chanty4u
MVP
MVP

Try this 

sum(aggr((Sum({<[p.Project Category]={'Customer work'}><[p.State.Name]={'Active','Completed'}>}[ActHours.Duration.value.Hours])/Sum({<[p.Project Category]={'Customer work'}><[p.State.Name]={'Active','Completed'}>}[ActHours.Duration.value.Hours])*[p.CommercialHREUR]), $(=GetFieldSelections(Period)), $(=GetFieldSelections([Debtor region])), ProjectID))

Kushal_Chawda

@cheryl  look at the getobjectfield() function. You can try below

sum(aggr((Sum({<[p.Project Category]={'Customer work'}>*<[p.State.Name]={'Active','Completed'}>}[ActHours.Duration.value.Hours])/Sum({<[p.Project Category]={'Customer work'}>*<[p.State.Name]={'Active','Completed'}>} total <Period,p.Program.Name> [ActHours.Duration.value.Hours])*[p.CommercialHREUR]),$(=GetObjectField(0)), $(=GetObjectField(1)), ProjectID))

cheryl
Contributor III
Contributor III
Author

Thanks Kushal,

 

I have changed the expression a bit and now it works!

sum(aggr((Sum({<[p.Project Category]={'Customer work'}>*<[p.State.Name]={'Active','Completed'}>}[ActHours.Duration.value.Hours])/Sum({<[p.Project Category]={'Customer work'}>*<[p.State.Name]={'Active','Completed'}>} total <$(=GetObjectField(0)), $(=GetObjectField(1))> [ActHours.Duration.value.Hours])*[p.CommercialHREUR]),$(=GetObjectField(0)), $(=GetObjectField(1)), ProjectID))

cheryl
Contributor III
Contributor III
Author

Thank you Chanty4u,

This expression didn't really work,  I have tried below and that works. but thanks for your reply!

sum(aggr((Sum({<[p.Project Category]={'Customer work'}>*<[p.State.Name]={'Active','Completed'}>}[ActHours.Duration.value.Hours])/Sum({<[p.Project Category]={'Customer work'}>*<[p.State.Name]={'Active','Completed'}>} total <$(=GetObjectField(0)), $(=GetObjectField(1))> [ActHours.Duration.value.Hours])*[p.CommercialHREUR]),$(=GetObjectField(0)), $(=GetObjectField(1)), ProjectID))