1

Design Table for WBS

Hi One consultan, I have a Database with some tables, ones of them are correspond with the structure of the project WBS Work Breakdown Structure I have four level.

Level 1 Project, Level 2 Area, Level 3 Subarea, and Level 4 Subtistem in each box Level are asociated documents by disciplines.

Now I Link Each level  to documents 1 to Many Level 1 to Documents, Level 2 to Documents etc. 

I don't sure if is the best options I trie too related Level 1, to Level 2 to Level 3 and Level 4 but these option is complicated.

Some Alternative help

I send image example 

62replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • Rafael
    • Rafael Sanchis
    • Rafael_Sanchis
    • 4 mths ago
    • Reported - view

    Hi Fred 
    I send you my DB
    1. First the tablet FechaCorte is where I change every week the cut-off date and the date is copied in all documents.

    2. Hitos is where I define the Milestones for document the same this 4 Milestones is copied for all document.

    3. Each documents has 4 Dates with Plan Dates and others 4 Days with Real dates.

    4. The Documen have  3 tab  thr first tab info of documents the second tab the Milestones and Dates Plan and Real Nd the las tab all hors info and progress Plan vs Real

    • Fred
    • Fred
    • 4 mths ago
    • Reported - view

    Hi Rafael -

    Thanks for sending me a copy of your DB.

    After reviewing it, the issue is still the same as you continue to reference the field Datadate and that field, like you say, is constantly changing. So Ninox has no reference point to find week 9 if you change the date to week 10 or 11 or 52. You need to have a field that stores date data that doesn't change so you can find what you want when searching.

    Rafael Sanchis said:
    3. Each documents has 4 Dates with Plan Dates and others 4 Days with Real dates.

    Please tell me how FechaP1-4 and FechaR1-4 are used. Maybe we can use these fields to get the totals you need.

    Like
      • Rafael
      • Rafael Sanchis
      • Rafael_Sanchis
      • 4 mths ago
      • Reported - view

      Fred Yes I used the the FechaP1 to P4, PlanDates, these dates are the time take the document to delivery for the 4 Milestones. The Fechas R1 to R4 are the Real Date.

      Compared the two Dates for all dates and all document give me the Curve S Real vs Plan 

      Document with 100 Hors

      Hito 1  Hito  2  Hito 3 Hito 4

      10%       45%      75%    100%

      11/03    18/03  24/03   01/04   Plan Dates   For the 01/04 earned the 100%

      11/03                                           The 3 Milestones without for date for the 01/04   no completed                                                            only   win   10%  10 hours I have a 90% of delayed.

      Like
    • Rafael
    • Rafael Sanchis
    • Rafael_Sanchis
    • 4 mths ago
    • Reported - view

    The idea is with the Hours week by week create that The Green curve is the Plan an the blue is the Real.

    The FechasP1 No change in the live of the projet.

    Like
    • Fred
    • Fred
    • 4 mths ago
    • Reported - view

    Can you try something like?

    sum((select Documentos)[week(FechaR1) = 9].'Horas Ganadas Plan')
    
    Like
      • Rafael
      • Rafael Sanchis
      • Rafael_Sanchis
      • 4 mths ago
      • Reported - view

      Fred Maybe let me try 

      Like
    • Rafael
    • Rafael Sanchis
    • Rafael_Sanchis
    • 4 mths ago
    • Reported - view

    😪 no working I try with one document 670 hors, with different Dates 04/03  11/03   18/03 an 25/03 and place all in semana 9

    For 10% 04/11  67  hours

    For 45% -10% 11/03  234.5 hours

    For 70%-45% 18/03  167.5 hours

    For 100%-30% 25/03 201 hors   For the 670  hours 

    Like
      • Rafael
      • Rafael Sanchis
      • Rafael_Sanchis
      • 4 mths ago
      • Reported - view

      Fred

      Thanks Fred sorry the last chance.

      Is use then else for each FechaP1, FechaP2, FechaP3, Fecha P4 with the same formula ?

      sum((select Documentos)[week(FechaR1) = 9].'Horas Ganadas Plan')

      Like
    • Fred
    • Fred
    • 4 mths ago
    • Reported - view

    Hi Rafael

    First, is this path a workable solution for you?

    If it is then that is an issue that would need to be dealt with. So the question is do you need to check each date for week 9? Or can you just check R4?

    Like
      • Rafael
      • Rafael Sanchis
      • Rafael_Sanchis
      • 4 mths ago
      • Reported - view

      Fred Hi Fred, I use these methodology for years in excel and work perfect, and now I will  like to work it in a Database.

      I need to cutt-off every week 9,10,11 is the only way to know the progress of the proyect.

      Again thanks a lot for you support appreciate.

      Like
    • Fred
    • Fred
    • 4 mths ago
    • Reported - view

    Hi Rafael -

    I am unsure where we are right now.

    Generally, when moving from Excel (a flat spreadsheet) to a relational database it is good to try to forget how Excel does it and allow your mind to learn a new way of doing things.

    I am having trouble wrapping my head around your workflow. Is the DB you uploaded setup like you described on your March 26 9:13am post? If not, can you upload a copy that has the data setup in a way that I can follow your question.

    Like
    • Rafael
    • Rafael Sanchis
    • Rafael_Sanchis
    • 4 mths ago
    • Reported - view

    Hi Fred let me 2 day and I send you a copy of another DB with all plan Dates by Documents and  Real dates by Documents. Is a small project a maybe more easy to you understand on this project you can change the DataDate and view how work the Progress of the Project. 

    Like 1
    • Rafael
    • Rafael Sanchis
    • Rafael_Sanchis
    • 4 mths ago
    • Reported - view

    Fred Hi Fred.

    I send you a copy of DB, is a real small project, have 114 Documents for 8weeks to begin to end the first data date was in 11/March and finish on 29/April all Plan Dates is define and the Real at the last cutoff date 25/ March

    You can change the Datadate in the table FechaCorte and in the Table Documents tab Totales you can view the % for Prgrss Plan vs Progress Real and Desviation of the project. 

    You can see 17.40 Plan vs 12.08 with desvition 5.33

    In the last table curva semanal there are the information that I need automatic now in place one by one.

    Try you change the Fecha Corte and you can view the chance on Progrees if you change the Fecha Corte to 29/April the project will be at 100%

      • Fred
      • Fred
      • 4 mths ago
      • Reported - view

      Rafael Sanchis give me a few days as I'm pretty busy right now.

      Like
      • Rafael
      • Rafael Sanchis
      • Rafael_Sanchis
      • 4 mths ago
      • Reported - view

      Fred Take your time no problem 👍

      Like
    • Rafael
    • Rafael Sanchis
    • Rafael_Sanchis
    • 4 mths ago
    • Reported - view

    The Red Curve is the Plan and the Gren the Real progress 

    The Real now is in week 12 there are any way to stop the Curve here no represent any week more.

    Like
    • Fred
    • Fred
    • 4 mths ago
    • Reported - view

    Hi Rafael

    Ok, well it looks to me (maybe someone else who is following along can come up with a better solution), that you don't track enough data to be able to figure out work done by week.

    1) I think you want to in Curva Semanal to be able to sum the Horas Ganadas (Plan and Real) for all records in Documentos by Semana.

    2) Let us start with the Horas Ganadas Plan. Since this number is set at the beginning of the project and the project is 8 weeks, it seems like to me that you would need to have either:

    a) 8 Ganado Plan fields, one for each week or

    b) you can create a plan subtable that tracks this data (useful if documents can have different number of planned milestones (hito)) . It looks like you have already thought of this route as I see a subtable called GanadaPlan.

    Looking at GanadaPlan, you don't need Flecha Corte here. This is just raw data of when you plan to have certain horas completed. You still point Semana back to datadate from Flecha Corte. I see you have Cutoff Date, so Semana should be point to Cutoff Date not datadate.

    3) Now to Horas Ganada Real, I still think you need to use a subtable that tracks the actual hours spent by date. Then you can find the weeks that match and sum the hours. So you have actual hours.

    Once we figure this out we can talk about dashboards and move the Totales tab out. For me it doesn't make sense to have a totals page in the Documentos table since it appears on every record, but no rush on this since it works for you.

    Like
    • Rafael
    • Rafael Sanchis
    • Rafael_Sanchis
    • 4 mths ago
    • Reported - view

    Hi Fred for take time for this.

    I send you again the DB, I create a new table avances with the 8 weeks the field % Acumulado Plan I take now manually from Table Documentos, Form Avances, field Avance Plan I cut off every friday and place the value manually 😔 that is the problem I require automatic because is small project is large project is a problem.

    I create a table Tablero Avances is a Dashboard look good I thing

    I hope you understand I'm not really good at English, and I only a month develop that DB 

    • Rafael
    • Rafael Sanchis
    • Rafael_Sanchis
    • 4 mths ago
    • Reported - view

    Fred

    The DB I send I think is better with the table Avances 

    The Data in these form is very important I have the Milestones (Hitos) the first line of Date are the Plan Dates  in the Curve the red line the second lines of dates are the Real Date that the document win the Milestone in the example the document win 3 Milestones 70% the cutoff is 25/03 the document is ok is not delay, but if for the same cutoffdate the document for Milestones 3 is not ready not include Date and are dekay because no win the hours for this Milestone. 

    Like
    • Fred
    • Fred
    • 4 mths ago
    • Reported - view
    Rafael Sanchis said:
    I send you again the DB, I create a new table avances with the 8 weeks the field % Acumulado Plan I take now manually from Table Documentos, Form Avances, field Avance Plan I cut off every friday and place the value manually  that is the problem I require automatic because is small project is large project is a problem.

    You are correct, you are creating more work for yourself. You need to create tables that capture the raw data and then you can manipulate it to fit your needs.

    In the Disciplinas table, are the horas hombre numbers the planned horas hombre for the project by discipline? So all electricidad documents are to equal 1,116 horas and so on?

    I have changed the ganadasplan table to ganadasreal and entered in some data to show how it could work. I've updated the avances.horas ganadas reales field to figure out the horas spent on the project.

    Does this work for you?

    • Rafael
    • Rafael Sanchis
    • Rafael_Sanchis
    • 4 mths ago
    • Reported - view

    Fred

    Yes but something hard 👍

    The second point yes for all Disciplinas if a check the hors For all Disciplinas, I try to sum the hours from Documents for each Activity & Disciplinas like a rollUp but I can't.

    Fred I have 3 kind of hours 

    The hours plan,  each hours is related with FechasPlan and Milestones, what the document need earned at the DataDate

    The Hours Real, each hours is related with FechasReal and Milestones what the document earned at DataDate

    Consumed Hours (Horas Consumidas) The people need to finish the Document. Hear need a table with Documents, People(s) Responsable of the finish Document, and DataDate I can have a maybe 3 o 4 people for each Discipline depend the project and hours 

    I have a long way to finish

    Like
    • Fred
    • Fred
    • 4 mths ago
    • Reported - view

    if you start tracking horas in ganadasReal then you can

    1) create a new formula field in the Disciplinas table:

    sum(Actividades.Documentos.GanadasReal.horas)
    

    This will find all related records in Documentos to the record in Disciplinas and add up all horas related to those documents.

    2) create a new formula field in Avances table:

    let t := this;
    sum((select GanadasReal)[Semana <= t.Semana].horas)
    

    This will find all records in the GanadasReal table that match the same week then add up all the horas spent during that week and before.

    I think you have to rethink how you use FlechaCorte.datadate. It sounds like to me it should be a dashboard page. You want to know what the status of your project is at a certain date. So you do to NOT need copy this data into your raw data but use it as a comparison.

    What I mean is that you want to know how many horas are planned and consumed by a certain date. That means you have to have raw date data that you can manipulate. If you look at the example above for the Avances table you can now see the totals of horas per week.

    Does this make sense?

    Like
    • Rafael
    • Rafael Sanchis
    • Rafael_Sanchis
    • 4 mths ago
    • Reported - view

    Fred

    Fred let me know something you thing the GanadasReal are consumed Hours ? 

    Like
    • Fred
    • Fred
    • 4 mths ago
    • Reported - view
    Rafael Sanchis said:
    I have 3 kind of hours 
    The hours plan,  each hours is related with FechasPlan and Milestones, what the document need earned at the DataDate
    The Hours Real, each hours is related with FechasReal and Milestones what the document earned at DataDate
    Consumed Hours (Horas Consumidas) The people need to finish the Document. Hear need a table with Documents, People(s) Responsable of the finish Document, and DataDate I can have a maybe 3 o 4 people for each Discipline depend the project and hours 

    Sorry, learning spanish as I go. :) Yes, ganadasreal should be horas comsumidas. To make is simpler you can just call it horas.

    I think you only have two types of raw data horas planned and horas cosumidas. Your horas real is just a formula of horas consumidas.

    I see you have planned horas by Disciplinas. I see in Documentos you have a field Horas Hombre. Is this supposed to be the total horas planned for the individual document? I don't think it can do both jobs of tracking horas planned and real.

    Like
    • Rafael
    • Rafael Sanchis
    • Rafael_Sanchis
    • 4 mths ago
    • Reported - view

    Fred

    Horas Ganadas Plan are the planned Hours the baseline hours of the project 

    Horas Ganadas Real are the Earned value it reflects the amount of hours that has actually been accomplished to data date.

    These two hours are calculate with Hitos(Milestones) and FechaP for planned Horus and FechaR for Earned Values.

    Horas Consumidas are the Actual Hours that has been expense, at datadate is not calculate is the profesional take to elaborate the document.

    Like
Like1 Follow
  • Status Answered
  • 1 Likes
  • 4 mths agoLast active
  • 62Replies
  • 284Views
  • 2 Following