Calculate weekly progress

[that_anonymous_guy]

Hello everyone!!

Problem statement - A project consists of multiple activities and work is done every week on certain activities. This is how my problem statement looks like. I want to calculate the weekly progress:

Project ID	Activity ID	Week	Progress	Weekly Progress
1	01	1	5%	5%
1	02	2	4%	4%
1	01	3	9%	4%
1	03	4	6%	6%
1	02	4	7%	3%
1	01	5	15%	6%

The goal is show a progress chart. For eg: W1..progress would be 5, W2..9, W3..13, W4..(6+3)so 22, W5..28.

Any help in how this can be achieved either in backend or front end?

Thanks in advance and do let me know if any further clarification is needed

[SerhanKaraer]

Hello,

You can do it with accumulation in charts.

[that_anonymous_guy]

yeah...that's true but weekly progress has to be calculated...I have shown in the example how it should look like. The problem is with calculating weekly progress. We need to find the difference between  last progress and current progress to get the weekly progress. For eg, in week 2 since the activity is being worked on for the first time so weekly progress will be 5 but in week 3 since activity 1 has already been worked on so weekly progress would be (9-5) i.e 4. I am not able to figure out how I can calculate this using Qlik

[SerhanKaraer]

Hi,

You have to order the input data to get difference by row with the help of previous function.

Tmp:
LOAD *, RecNo() as recno INLINE [
Project ID	Activity ID	Week	Progress
1	01	1	5%
1	02	2	4%
1	01	3	9%
1	03	4	6%
1	02	4	7%
1	01	5	15%
] (delimiter is '\t');

Join
  LOAD "Project ID", "Activity ID", Week, Progress, 
  if(RecNo() = 1 or not(Previous("Project ID") = "Project ID" and Previous("Activity ID") = "Activity ID"),
      Progress, num(Progress - Previous(Progress),'0%')) as "Weekly Progress"
  Resident Tmp
  ORDER BY "Project ID", "Activity ID", Week;

Fact:
NoConcatenate
LOAD "Project ID", "Activity ID", Week, Progress, "Weekly Progress" Resident Tmp ORDER BY recno;

Drop Table Tmp;

 I hope it solves your issue.

[MayilVahanan · Accepted solution]

Hi 

Try like below

Temp:
LOAD * INLINE [
Project ID, Activity ID, Week, Progress
1, 01, 1, 5%
1, 02, 2, 4%
1, 01, 3, 9%
1, 03, 4, 6%
1, 02, 4, 7%
1, 01, 5, 15%
];

Load *, if([Project ID] = Previous([Project ID]) and [Activity ID] = Previous([Activity ID]), Num(Progress - Previous(Progress), '#%'), Progress) as WeeklyProgress
Resident Temp
Order by [Project ID], [Activity ID];

DROP Table Temp;

[that_anonymous_guy]

thanks @SerhanKaraer @MayilVahanan for your inputs.it is working now