Forum Discussion

juan jimenez's avatar
juan jimenez
Iron Contributor
Apr 09, 2019

Dynamic tables

I am trying to analyze time spent in four different activities realized everyday. Please see attachd excel sheet.   I'm trying to use "Dynamic tables" in order to get time spent per day in minutes ...
Dynamic tables
juan jimenez's avatar
juan jimenez
Iron Contributor
Apr 17, 2019

SergeiBaklan 

 

Dear Sergei, thank you very much for the table and help.

 

I have taken some time to answer back as this is new for me and I need a lot of time to understand and avance.

 

I hope if you don't mind that I keep on asking in a few weeks once I have all my questions together.

 

I wish you a nice week, Juan.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 17, 2019

juan jimenez 

 

Hi Juan,

 

Sure, don't hesitate to ask any question. I don't promise immediate answers, there are periods when I'm overloaded by other stuff. And there is always a chance if someone else comments, lot of great experts are here.

  • juan jimenez's avatar
    juan jimenez
    Iron Contributor
    Apr 18, 2019

    Thank you very much for your disposal to help.

     

    I have being testing myself with the format options and some other features.

     

    Could you tell me :

     

    1. How did you manage to introduce “meses”  on “promedios” sheet if this is not an existing column in the data source?
    2. Is there a way to split weekdays and weekends so that average of working or resting time is more accurate?
    3. What would it mean the average of minutes as it seems not to have any sense in this case?
    4. If I want to know the total for every year, do I have to introduce a column in the data sheet for the year number?
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 18, 2019
      1. What would it mean the average of minutes as it seems not to have any sense in this case?

      That's just sum of minutes for all records in the period divided on number of such records, I don't know what that's all about and does it have any sense from business logic point of view.

       

      1. If I want to know the total for every year, do I have to introduce a column in the data sheet for the year number?

      Yes, just add one more grouping by years

      • juan jimenez's avatar
        juan jimenez
        Iron Contributor
        Apr 19, 2019

        SergeiBaklan 

         

        Once again your help has been very useful  for understanding pivot tables and taking advantage of my data sheet. Thank you very much indeed.

         

        The only field that I still cannot understand and develop properly is the “average” funtion. I would like to know how to calculate averages of daily, weekly, monthly and year activities... 

         

        What would you suggest or recommend to understand and learn this area?

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 18, 2019
      1. Is there a way to split weekdays and weekends so that average of working or resting time is more accurate?

      You may add one more column to your source data, in simplest variant

      =IF(WEEKDAY(B2,2)<6,"Weekday", "Weekend")

      and add it to you hierarchy in Pivot Table

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 18, 2019

      Hi Juan,

       

      One by one

      1. How did you manage to introduce “meses”  on “promedios” sheet if this is not an existing column in the data source?

      If you right click on the column with dates in PivotTable and select Grouping when it'll be added all your grouped columns (months, etc)

       

Resources