Set Statement needed for Summarizing hours based on Start and End Date of 1 of 7 phases in a Project

[JayG]

Hello ,

I have two dimension fields called "Phase" and "Project", with a measurement field of "Hours" that I will be using in a visualization. My data includes payroll data with hours worked and also a list of projects with 7 phases, containing start and end dates (fields names are "StartDate" and "EndDate" respectively). I am trying to create a Set Expression that will look at the payroll data (that includes date and hours worked) and summarize the hours worked by each of the 7 phases, based on which phase the date worked, falls into. For example, if an employee worked on Prioject Alpha for 40 hours from 9/6/21 - 9/10/21, and another 40 hours from 9/20/21 - 9/24/21, and "Project" Alpha - Phase 1 has "StartDate" = 9/1/21  and "EndDate" 9/30/21,  the set expression would result in a visualization that displays:

Phase 1 -Summary of 80 hours - for Project Alpha.

[JayG]

Hello ,

I have two dimension fields called "Phase" and "Project", with a measurement field of "Hours" that I will be using in a visualization. My data includes payroll data with hours worked and also a list of projects with 7 phases, containing start and end dates (fields names are "StartDate" and "EndDate" respectively). I am trying to create a Set Expression that will look at the payroll data (that includes date and hours worked) and summarize the hours worked by each of the 7 phases, based on which phase the date worked, falls into. For example, if an employee worked on Prioject Alpha for 40 hours from 9/6/21 - 9/10/21, and another 40 hours from 9/20/21 - 9/24/21, and "Project" Alpha - Phase 1 has "StartDate" = 9/1/21  and "EndDate" 9/30/21,  the set expression would result in a visualization that displays:

Phase 1 -Summary of 80 hours - for Project Alpha.

[canerkan]

Hi JayG,

i would recommend to manipulate your datasets into having a set for each date thats between the StartDate and EndDate, e.g.

Original Dataset:

StartDate = 9/1/21 - EndDate = 9/5/21 - Project = Alpha - Phase = 1

Manipulated Datasets:

StartDate = 9/1/21 - EndDate = 9/5/21 - Project = Alpha - Phase = 1 - newDate= 9/1/21

StartDate = 9/1/21 - EndDate = 9/5/21 - Project = Alpha - Phase = 1 - newDate=  9/2/21

StartDate = 9/1/21 - EndDate = 9/5/21 - Project = Alpha - Phase = 1 - newDate= 9/3/21

StartDate = 9/1/21 - EndDate = 9/5/21 - Project = Alpha - Phase = 1 - newDate= 9/4/21

StartDate = 9/1/21 - EndDate = 9/5/21 - Project = Alpha - Phase = 1 - newDate= 9/5/21

you can do that by using IterNo():

Projects:

Load

...

IterNo() -1 + StartDate as newDate,

ProjectName & '_' & IterNo() -1 + StartDate as %Key

From ...

while  IterNo()-1 + StartDate <= EndDate;

Do the same with your payrolls table and connect the tables by using the field %Key to make sure each Dataset in your payrolls table is connected to the proper Dataset in your Project Table, in case you have Projects that are worked on simultaneously, e.g. 9/1/21 Project Alpha, 9/1/21 Project Beta

Note that in case your payroll table only contains the total of worked hours during the period between StartDate and EndDate you will have to divide that hours too and use this field later on in your Sum() Function of your Visualisation, e.g.:

Payroll:

Load

...

WorkedHours / (EndDate - StartDate) as DailyHours,

IterNo() -1 + StartDate as newDate2,

ProjectName & '_' & IterNo() -1 + StartDate as %Key

From ...

while  IterNo()-1 + StartDate <= EndDate;

Hope this helps,

Can

[JayG]

Thanks. I will give that a try.