Weighted average in bar char based on the selected dimension

[cheryl]

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.

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

[Chanty4u]

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 · Accepted solution]

@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 · Accepted solution]

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]

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