Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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))
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))
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))
@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))
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))
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))