Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculating Project Cost

Hi All,

I am trying to make a operational dashboard.

the original excel table looks pretty much the same with the table you see on the lower left corner.

I want to figure out a way to effectively show the cost of each project.

my dimension is "Project Name" and I set my expression as

there is one problem with my approach. It works fine when all the projects share same star and end date for all of the resources.

but if person A and person B in same project have different start and end date for the project, it does not give me the correct calculation of overall project cost and I know that it is because I set my dimension as "Project Name." and I know that it is because I have set the expression as Max([End Date]) - Min([Start Date]). it will pick up the latest end date and earliest start date.

can anyone help me with a way to calculate the cost of each project correctly even though every individual involved in the project does not have the same duration of involvement into the project and when everybody has different rate?

Thanks!!

1 Solution

Accepted Solutions
Not applicable
Author

=sum(   ([End Date]-[Start Date]) *([Pay per day])        

)

this worked!

View solution in original post

6 Replies
settu_periasamy
Master III
Master III

Maybe try something like this..

=Aggr(Max([End Date])-Min([Start Date])*sum([Pay per day]),[Project Name])

Peter_Cammaert
Partner
Partner

Apparently, the fact that employees that participate in a particular project can have different start dates, end dates and hourly rates makes the aggregation expression a bit more complex.

How about:

=sum(aggr(sum((alt([End Date], today())-[Start Date])*[Pay per day])), [Employee Name], [Project Name]))

Maybe you should post an example document with a representative data set for us to experiment on.

Peter

Not applicable
Author

sorry it did not work. I am sure there should be a way but little complicated to come up with it...

Not applicable
Author

sorry it did not work. I am sure there should be a way but little complicated to come up with it...

Not applicable
Author

NameRoleProject NameProject OwnerTimingStart DateEnd DatePay per hourPay per day
Daryl DixonEnglishOngoingPaulOngoing4/28/201614112
Rick GrimesEnglishProject ADavidTerm4/28/20165/28/201615120
GlennRheeEnglishProject ADavidTerm4/28/20165/28/201616128
Maggie GreeneEnglishOngoingDavidOngoing4/28/201617136
MichonneTLOngoingDavidOngoing4/28/201618144
Carol PeletierKOR IIProject BPaulTerm5/28/20166/28/201619152
Carl GrimesEnglishProject BPaulTerm5/28/20166/28/201620160
Beth GreeneEnglishOngoingPaulOngoing4/28/201621168
Sasha WilliamsEnglishOngoingPaulOngoing4/28/201622176
Abraham FordJPN IIOngoingDavidOngoing4/28/201623184
AndreaTLOngoingDavidOngoing4/28/201624192
Lori GrimesJPN IIProject CPaulTerm4/28/20166/28/201625200
Rosita EspinosaEnglishProject CPaulTerm4/28/20167/28/201626208
TyreeseEnglishProject CPaulTerm4/28/20167/28/201627216
Eugene PorterEnglishProject DPaulOngoing4/28/20165/28/201628224
Eugene PorterEnglishProject EPaulOngoing5/28/20166/28/201628224
Eugene PorterEnglishProject FPaulOngoing6/28/20167/28/201628224

Here is my example!! thank you!!

Not applicable
Author

=sum(   ([End Date]-[Start Date]) *([Pay per day])        

)

this worked!