0

I have relationship with 2 tables

Table 1 - Process

enter image description here

Table 2 - Process History

enter image description here

Here the relationship is Id(Process table) and ProcessId(Process history table) I want to calculate the Average Networking days of all the processes.

For eg: 

nwd = 0;
count = 0;
if(Process.Id = ProcessHistory.ProcessId && ProcessHistory.Status='Status 3') {
  nwd += NWD(Process.CreatedOn, ProcessHistory.CreatedOn);
  count++;
}

Expected result AverageNWD = nwd/count;

How can we achieve this?

Gnik
  • 7,120
  • 20
  • 79
  • 129

1 Answers1

2

In the script:

Using the script below will add a new field to the Process table - NetWorkingDays. This field will contain the working days for each project (Id). With this field in the dataset will be easier to calculate the average in the UI (something like sum(NetWorkingDays) / count(distinct Id)

Process:
Load * Inline [
Id, Name    , CretedOn
1,  Process1, 2019-04-02
2,  Process2, 2019-04-05
3,  Process3, 2019-05-02
4,  Process4, 2019-06-02
];


ProcessHistory:
Load 
  Id        as ProcessHistoryId,
  ProcessId as Id,
  Status,
  CreatedOn as ProcessHistoryCreatedOn
;
Load * Inline [
Id, ProcessId, Status  , CreatedOn
1,  1,         Status 1, 2019-04-02
2,  1,         Status 2, 2019-04-02
3,  1,         Status 3, 2019-04-04
4,  2,         Status 1, 2019-04-05
5,  2,         Status 3, 2019-04-06
6,  3,         Status 1, 2019-05-07
7,  3,         Status 3, 2019-05-09
8,  4,         Status 1, 2019-06-02
9,  4,         Status 2, 2019-06-04
10, 4,         Status 3, 2019-06-07
];


TempTable:
Load
  Id,
  min(CretedOn) as MinCreatedOn
Resident
  Process
Group By
  Id
;

join (TempTable)

Load
  Id,
  max(ProcessHistoryCreatedOn) as MaxCreatedOn
Resident
  ProcessHistory
Where
  Status = 'Status 3'
Group By
  Id
;


NetWorkingDaysData:
Load
  Id,
  NetWorkDays(MinCreatedOn, MaxCreatedOn) as NetWorkingDays 
Resident
  TempTable
;

Drop Table TempTable;

The last part of the script (from inside out):

Create temporary table to calculate min(CreatedOn) from Process table and max(ProcessHistoryCreatedOn) from ProcessHistory table. ProcessHistory is also filtered to include only records where Status = 'Status 3' (both tables are aggregated per Id)

TempTable:
Load
  Id,
  min(CretedOn) as MinCreatedOn
Resident
  Process
Group By
  Id
;

join (TempTable)

Load
  Id,
  max(ProcessHistoryCreatedOn) as MaxCreatedOn
Resident
  ProcessHistory
Where
  Status = 'Status 3'
Group By
  Id
;

Once the temp table is created we can create the final table that in which we will calculate the number of net working days using the NetWorkDays function. The NetWorkingDaysData table will have only two fields - Id and NetWorkingDays 

NetWorkingDaysData:
Load
  Id,
  NetWorkDays(MinCreatedOn, MaxCreatedOn) as NetWorkingDays 
Resident
  TempTable
;

And the final step is to drop the TempTable - its no longer required

In the UI:

The same result can be achieved in the UI using the expression below. Just bear in mind that the UI approach might lead to higher resource consumption! Since all the calculations are on-the-fly (depends how big your dataset is)

avg(
  Aggr(
    NetWorkDays( min(ProcessHistoryCreatedOn) , max( {< Status = {'Status 3'} >} ProcessHistoryCreatedOn) )
  , Id)
)
Stefan Stoichev
  • 4,615
  • 3
  • 31
  • 51
  • Stefan, Thanks for your valuable answer. Will try this and update you. I have a question min(ProcessHistoryCreatedOn) as MinCreatedOn, max(ProcessHistoryCreatedOn) as MaxCreatedOn - Both are taken from ProcessHistory table. But my requirement is Min needs to be taken from Process.CreatedOn. Also the ProcessHistory.CreatedOn needs to be taken when Status='Status 3'. So it will be more helpful if you edit your answer accordingly – Gnik Jun 24 '19 at 17:13
  • @Prince updated the answer to include your comment. Out of curiosity ... why the Min CreatedOn should be taken from the Process table? – Stefan Stoichev Jun 24 '19 at 19:04
  • @Stephen Stoichev, Actually I want to take the NWD between the entry in Process table and the 'Status 3' entry in ProcessHistory table (ProcessHistory 'Status 1' entry can be a different date than the Process table entry. – Gnik Jun 26 '19 at 05:33