Ask Your Question
0

Fill data serie in a pivot table

asked 2019-02-09 11:58:24 +0200

Square34 gravatar image

Hi all, I have a data source sheet with multiple entry by days of the month but not for all days of the month. Eg following:

  1. Validity Data(d.m.y) Hours(dec)
  2. @08@ 02.01.2019 2.75
  3. @08@ 02.01.2019 3.00
  4. @08@ 03.01.2019 3.00
  5. @08@ 03.01.2019 3.00
  6. @08@ 03.01.2019 1.40
  7. @08@ 04.01.2019 1.00
  8. @08@ 04.01.2019 4.00
  9. @08@ 07.01.2019 3.00
  10. @08@ 07.01.2019 2.90
  11. @08@ 07.01.2019 3.00
  12. @EB@ 10.01.2019 3.00
  13. @EB@ 10.01.2019 3.00
  14. @08@ 10.01.2019 2.00

I need to create a pivot which sum "Hours" by "Data" when "Validity"=@08@ but for all days of the month even if they are not present like following:

  • Date TOTH
  • 01.01.2019 0.00
  • 02.01.2019 5.75
  • 03.01.2019 7.40
  • 04.01.2019 5.00
  • 05.01.2019 0.00
  • 06.01.2019 0.00
  • 07.01.2019 9.90
  • 08.01.2019 0.00
  • 09.01.2019 0.00
  • 10.01.2019 2.00
  • 11.01.2019 0.00
  • 12.01.2019 0.00 ...

I manage a pivot with daily sum and Validity=@08@ can be as filter, but i cannot manage to fill days with no data from the source sheet.

Thanks in advance for any sggestion

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-02-09 13:24:09 +0200

Lupp gravatar image

updated 2019-02-09 13:29:55 +0200

See this attached example sheet for a raw suggestion by the tinkerer. You probably will need to move the helper part without time values to higher or lower row numbers I assumed 10000 datasets per month should be enough.

The heplers must be in the VALIDITY and in DATE column respectively, of course.

edit flag offensive delete link more

Comments

Hi Lupp,

Thanks a lot for your suggestion, it work fine. While I'm trying to find a completely automated solution... I have to do this each month for the previous month, and with your suggestion, each time I have "modify" the source table and "enrich" it with the monthly full day (your helper part work also well), then I have to create the new pivot.

If possible I need to leave the source sheet untouched (it can be a read-only sheet), and I was wondering if there's a way to let pivot calculate values to list in rows while those are in series.

Anyway you save my day ;)

Square34 gravatar imageSquare34 ( 2019-02-09 15:47:46 +0200 )edit

Quoting @Square34: "...While I'm trying to find a completely automated solution... I have to do this each month for the previous month, and with your suggestion, each time I have "modify" the source table and "enrich" it with the monthly full day (your helper part work also well), then I have to create the new pivot."
You will surely use an otherwise empty sheet (copying a kind of template) then. You will, however need to adapt the sourtce range the by 'Edit Layout'. The helpers don't need manual adaption.. Thjey adapt correctly based on the date in C2.
But: Even more autoiamtion you only can achieve with specialised ueser code..I doubt whether that pays.

Lupp gravatar imageLupp ( 2019-02-09 22:02:40 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-02-09 11:58:24 +0200

Seen: 23 times

Last updated: Feb 09