Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculation cost for each product for designated period of time

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?

and another question is,

I want to be able to get monthly or quarterly cost for each project. I want to do it my clicking on month name or quarter name. but the way my bars are presented on the gantt chart is by max(end date)-min(start date) so it only represents length but not telling qlikview from which day to which day. So I don't know how I should proceed with this too. Please share your insight community!!

Thanks!!

1 Solution

Accepted Solutions
Not applicable
Author

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

I was able to answer the first question myself! But still the second question remains!

View solution in original post

2 Replies
Not applicable
Author

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

I was able to answer the first question myself! But still the second question remains!

jonas_rezende
Specialist
Specialist

Hi, david oh.

See below answer per question:

  1. Sum(

              Aggr(Max([End Date]) - Max([Start Date]) * Sum([Pay per day])

              , [Project Name]

              )

        ) //Get date max and min of each project and multiply by of metric.

   2. The my idea is, in your script, create a table with monthly or quarterly cost associated with the number or  project name. So then, join in data model. Ex.:

Table_exam:

Load

Autonumber(Project Name)    as  [%Project Number],

Cost,

Month,

Quarter

from <name table>.qvd

(qvd);

Hope this helps!