Log In
Register
Home
January 12, 2021
Offline
Hi all,
I have a question about pivot and results for an overview.
Attached there is an excel file, with an table of tasks from A1:H25.
There are different dates enteres, which describes, which task passed into next Phase. So an regular result is doable, like my both pivots in K2:V4 and K8:U10. So I can figure out, how many tasks where entered, and passed into a next phase.
My challange is no, to figure out, how many tasks where open at the end of an month. From K32:W37 I´ve tried to explain this:
- Task_05 passed Phase 1 in January and turned into Phase 2. As this was finalized in March, this task was in end of January and Februrary open. (orange highlighted)
- Task_02 and Task_14 passed Phase 1 in March and turned into Phase 2. As one was finalized in April and one in May, there where 2 tasks open at the end of March, and one at the end of April. (green highlighted)
- In september I get 7 results, as they passed Phase 1 before end of September and Phase 2 after. (light red highlighted)
- As the blue ones passed in same month, they should ignored.
Is there any chance, to make this visible in one Pivot for the different Phases?
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Unfortunately, I don't think it's possible.
To show a task as open in months 1 and 2 and closed from month 3 onward, you need to have a status column at the end of each month.
Based on your initial table, you have to write a custom vba code that populates another table, properly structured for the desired output.
Initial table must be converted to a tabular structure, like:
| Tasks | Phase | Date |
| Task_01 | 0 | 12/03/2021 |
| Task_01 | 1 | 12/03/2021 |
| Task_01 | 2 | 31/03/2021 |
| Task_01 | 6 | 12/04/2021 |
Answers Post
January 12, 2021
Offline
Hello Catalin,
many thanks for your answer 🙂
Unfortunately I have no experience with VBA.
Will it possible, to get these structure also with Power Query?
Here I´m also not an expert, but I´m able to understand this much better than vba 🙂
January 12, 2021
Offline
Hello Catalin again,
🙂
I think I got it now on Power Query.
On line F46 of the attached file it works 🙂
Unfortunately I have now all dates in my Pivot on K46.
"Task 01" passed in 03/2021 from Phase0 up to Phase2. In this case it jumed immediately to Phase6 and we don´t have a dates from Phases between.
So this task remains already end of March in Phase 6 and was finalized in April.
So I have now threee entries in March, and one in April, but I need just one for "remaining in Phase 6" in April.
Did I something wrong?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Instead of removing null dates, sort tasks and phases properly and Fill down the date column, for Task1 as an example it will populate Phases 3-5 with phase 2 date. Is this what you wanted?
Quelle = Table.Combine({Phase0, Phase1, Phase2, Phase3, Phase4, Phase5, Phase6}),
#"Sorted Rows" = Table.Sort(Quelle,{{"Tasks", Order.Ascending}, {"Phase", Order.Ascending}}),
#"Filled Down" = Table.FillDown(#"Sorted Rows",{"Date"})
in
#"Filled Down"
1 Guest(s)
