Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi!
I´ve been struggling alot lately with a replacement plan.
Im working on a replacement plan report for welding machines at work, what I have is a list of all welding machines we have in all plants, with the machines status ( Current, Transitioning, Obsolete). What we want is to make a replacement plan so we can keep up with the machines changing status. So I have 3 tables, the one I meantioned earlier with all the welding machines, I have a second one with the machines model status over the years and a third one where i put how many new machines we are going to buy per plant.
So what i want to do, is add the number of new machines to the current count and substract it from the obsolete ones and in case there are no more obsoletes starts substracting from de transitioning.
I´ll Put some examples of tables.
Table 1
Plant | Model |
PT | Model A |
ST | Model A |
ST | Model A |
FM | Model C |
PT | Model B |
PT | Model C |
ST | Model A |
FM | Model B |
PT | Model C |
FM | Model A |
ST | Model B |
Table 2
Model | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 |
Model A | Current | Current | Current | Current | Transitioning | Transitioning |
Model B | Current | Transitioning | Transitioning | Transitioning | Obsolete | Obsolete |
Model C | Transitioning | Transitioning | Obsolete | Obsolete | Obsolete | Obsolete |
Table 3
Plant | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 |
PT | 1 | 2 | 0 | 0 | 1 | 1 |
ST | 1 | 1 | 1 | 2 | 0 | 1 |
FM | 1 | 1 | 0 | 0 | 0 | 1 |
What I´ve done so far is merging Table 1 and Table 2 and then unpivoting the year. I´ve tried alot of things but none seem to work.
What i want is to do a scatter chart with the number of current, transitioning and obsolete machines over the years, I dont care about the models, just the numbers.
What I´ve been trying is getting the row count filtered by year and plant and add the number o new machines from Table 3 but I´ve been unable to do it since im new and i dont know very much.
If anyone has any idea how to tackle this problem i would be really grateful
Hi @Anonymous ,
Could we read your tables as follows? Just trying to get a handle on them.
Plant PT currently has 4 machines, A,B, and 2 Cs. Plant PT will buy 5 machines over the time period. 3 machines will go obsolete, and 1 will transition, so at the end of the time period, we will have 3 extra machines?
Thanks,
Nathaniel
Plant | PT | Models | |||
A | B | C | |||
Current | ??? | 1 | 1 | 2 | 4 |
2019 | 1 | 5 | |||
2020 | 2 | 7 | |||
2021 | 0 | ob | 5 | ||
2022 | 0 | 5 | |||
2023 | 1 | ob | 4 | ||
2024 | 1 | tr | 3 |
Proud to be a Super User!
Yes, we will have extra machines
I left out two tr. So for PT is this how you would read each year?
Plant | PT | Models | |||
A | B | C | |||
Current | ??? | 1 | 1 | 2 | 4 |
2019 | 1 | tr | 4 | ||
2020 | 2 | tr | 5 | ||
2021 | 0 | ob | 4 | ||
2022 | 0 | 4 | |||
2023 | 1 | ob | 4 | ||
2024 | 1 | tr | 3 |
Proud to be a Super User!
This is how i see your table, i cant understand it
I will work with yours. I will be offline for awhile.
Proud to be a Super User!
Hi @Anonymous ,
Would it be possible to get a table like you have given us, but with your expected output. You should be able to dummy it up in Excel.
Thank you,
Nathaniel
Proud to be a Super User!
Sure, First I need something like this
Plant | Status | Year | Count | W/ Replacement Plan |
PT | Current | 2019 | 2 | 3 |
PT | Transitioning | 2019 | 2 | 1 |
PT | Obsolete | 2019 | 0 | 0 |
PT | Current | 2020 | 2 | 4 |
PT | Transitioning | 2020 | 3 | 1 |
PT | Obsolete | 2020 | 0 | 0 |
PT | Current | 2021 | 4 | 4 |
PT | Transitioning | 2021 | 0 | 0 |
PT | Obsolete | 2021 | 1 | 1 |
ST | Current | 2019 | 4 | 5 |
ST | Transitioning | 2019 | 0 | 0 |
ST | Obsolete | 2019 | 0 | 0 |
ST | Current | 2020 | 4 | 5 |
ST | Transitioning | 2020 | 1 | 0 |
ST | Obsolete | 2020 | 0 | 0 |
Then In my scatter chart i need something like this
Status | Year | Count |
Current | 2019 | 8 |
Transitioning | 2019 | 1 |
Obsolete | 2019 | 0 |
Current | 2020 | 9 |
Transitioning | 2020 | 1 |
Obsolete | 2020 | 0 |
Thank you. A picture is worth a thousand words!
Proud to be a Super User!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |