topic Re: Calculating Project Cost in QlikView

Calculating Project Cost

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculating Project Cost

settu_periasamy — Wed, 04 May 2016 18:22:06 GMT

Maybe try something like this..

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

Re: Calculating Project Cost

Peter_Cammaert — Wed, 04 May 2016 18:50:10 GMT

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

Re: Calculating Project Cost

Wed, 04 May 2016 19:49:24 GMT

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

Re: Calculating Project Cost

Wed, 04 May 2016 19:49:35 GMT

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

Re: Calculating Project Cost

Wed, 04 May 2016 20:02:51 GMT

Name	Role	Project Name	Project Owner	Timing	Start Date	End Date	Pay per hour	Pay per day
Daryl Dixon	English	Ongoing	Paul	Ongoing	4/28/2016		14	112
Rick Grimes	English	Project A	David	Term	4/28/2016	5/28/2016	15	120
GlennRhee	English	Project A	David	Term	4/28/2016	5/28/2016	16	128
Maggie Greene	English	Ongoing	David	Ongoing	4/28/2016		17	136
Michonne	TL	Ongoing	David	Ongoing	4/28/2016		18	144
Carol Peletier	KOR II	Project B	Paul	Term	5/28/2016	6/28/2016	19	152
Carl Grimes	English	Project B	Paul	Term	5/28/2016	6/28/2016	20	160
Beth Greene	English	Ongoing	Paul	Ongoing	4/28/2016		21	168
Sasha Williams	English	Ongoing	Paul	Ongoing	4/28/2016		22	176
Abraham Ford	JPN II	Ongoing	David	Ongoing	4/28/2016		23	184
Andrea	TL	Ongoing	David	Ongoing	4/28/2016		24	192
Lori Grimes	JPN II	Project C	Paul	Term	4/28/2016	6/28/2016	25	200
Rosita Espinosa	English	Project C	Paul	Term	4/28/2016	7/28/2016	26	208
Tyreese	English	Project C	Paul	Term	4/28/2016	7/28/2016	27	216
Eugene Porter	English	Project D	Paul	Ongoing	4/28/2016	5/28/2016	28	224
Eugene Porter	English	Project E	Paul	Ongoing	5/28/2016	6/28/2016	28	224
Eugene Porter	English	Project F	Paul	Ongoing	6/28/2016	7/28/2016	28	224

Here is my example!! thank you!!

Re: Calculating Project Cost

Wed, 04 May 2016 22:19:46 GMT

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

)

this worked!