Working out the Delta between timewcards approved and yet to be completed

[Voose]

Hi All,

I'm trying to work out how I can calclate the amount of time that has not been submitted by a resource to date. I already have a few elements:

Total amount workable days to date in this calendar month

Total amount of submitted days in this calendar week

The Calender week is house in Timecard Split which links to timecard via the Unique ID, Timecard links to the resource via the resource ID and resource links to the Utilisation Detail object via the resource ID as well (all relationships are bi-directional).

If I try to create a column in Timecard Split to pull in the number of workable days to date in this calendar month it just doesn't give me the option & therefore I cannot minus one from the other in the timecard split object...

Any one else had some luck with the above?

Thanks

[Greg_Deckler]

Can you supply some sample data so that we can model it?

[Voose]

@Greg_Deckler Sure how can i attach a file? 

Thanks

[Voose]

Just reliased i can copy and paste here so please find below a small subset of data as an illustrative example (can't give to much more just as company data etc)
Please also find the Relationship map attached.

Looking forwards to your thoughts.

Thanks

Timecard Split:
 

Resource	Total Days Worked	Start Date	Timecard: Timecard Id	Timecard Split: Timecard Split Id
AAA	0.75	06/02/2017	TCH-02-10-2017-008153	TC-02-10-2017-009311
AAA	3.25	06/02/2017	TCH-02-10-2017-008152	TC-02-10-2017-009312

Time Card:

Resource	Total Days Worked	Start Date	Timecard: Timecard Id
AAA	3.25	06/02/2017	TCH-02-10-2017-008152
AAA	0.75	06/02/2017	TCH-02-10-2017-008153

Contact :

Resource

AAA

Utilisation Detail:

Resource	Historical Calendar Hours	Time Period	Utilization Calculation: Utilization Calculation Name	Utilization Detail: Utilization Detail Id
AAA	160	Feb-17	Global Utilization Calculation	UD170783

This is all in the Force.com platform.

The relationship map is as follows:

Timrcard split ID <-> ID Timecard  

Timecard - Resource <-> ContactID

ContactID <-> Utilisation Detail - Resource

[v-qiuyu-msft]

Hi @Voose,

Regarding the sample data, what's the desired results you want? Can you elaborate "the amount of time that has not been submitted by a resource to date"?

Best Regards,
Qiuyun Yu

[Voose]

Hello again @v-qiuyu-msft!

Realise my previous description was poor, what I would like to do is as follows, I have a bunh of names and next to them their timecards and if they are approved /saved / submitted and a total as shown below:

 I want to do a few things:

Firstly - Create a column that I can display within this table that shows the amount of working calendar days past so far in the current month upto the last friday (if the month starts on a wednesday like March then i want Wed - friday included) for exmaple for day of posting this I would want the column to = 8.

Things to note - The above timecard information is not stored in 1 row in the underlying data as the resources may store their time in small chunks. For example the first row of data shows 8 days approved, this could be made up of 8 X16 4 hour rows as you can see later on in the table it starts to give some odd numbers.

Once the above is done I would then like to create a to be saved column, to be submitted and to be approved. these are farily basic columns:

A total days so far	B approved	C saved	D sub	E to be saved	F to be submitted	G to be approved
8	8	5	0	-5	0	0
8	3	0	0	5	0	0
8	3	0	5	0	0	5
8	3	0	0	5	5	0

Assuming the far left one is col A the formulae in excel would be as follows:

To be saved = =A33-(B33+C33+D33)

To be submitted = =A33-(B33+D33)

To be approved = =D33

Looking forward to your thougts!

Thanks

[Voose]

Still struggling with this one chaps have been trying to think of a solution here and have come up with one that is plausible just unsure how to build it into BI.

Idea being that I could create a calculated table for each status of time card I.E. create an approved table that shows only approved timecards for each resource and summarises all of the hours / timecards for one person for a particular day. 

Hope someone can help me here - struggling with this one 😞