Log In
Register
Home
March 3, 2021
Offline
I wanted to get the running total per material, but it also has to consider the release number.
For example, I have a material number M0171L010, different release numbers 267, 268, 269, etc.
Like below I would like to see it, but don't know what the reason is that I don't get it. I added a file as example.
Rel nr Cum.RecQty wk27 wk28 wk 29 wk30 wk31 wk32 .... wk38 wk39
267 549932 Scheduled qty 4200 4060 6720 0 3500 0 0 9380 1400
Running total 554132 558192 564912 564912 568412 568412 568412 577792 579192
268 570092 Scheduld qty 0 0 0 0 0 0 0 6720 1400
Running total 570092 570092 570092 570092 570092 570092 570092 576812 578212
Trusted Members
Moderators
January 31, 2022
Offline
Hi Johan,
You pasted several lines of text. Difficult to "translate" this into useful data. Can you upload a file? Press "Attachments" below the box where you enter your response and then select a file and press "Start upload".
Riny
Trusted Members
February 13, 2021
Offline
Hi Johan!
While waiting for your example file, here is an article on how to do running totals in PQ.
March 3, 2021
Offline
Hey Riny,
Sorry, I though that I added the file.
Trusted Members
Moderators
January 31, 2022
Offline
Thanks for the file. It took a while until I figured out what you were trying to do and concluded you needed to build the pivot table with the second waterfall report as a basis. Needed to add a unique key to both waterfalls so that I could establish a relationship between the two. Started to tinker with your pivot table but screwed it up. Sorry! See if the pt below "yours" achieves what you need. I.e. the slicercs and the pt starting in row 28. If not, come back.
Riny
March 3, 2021
Offline
Hello Riny, thanks, this is indeed what I want. It's to compare the releases from the customer.
But it's strange, now you added the Key, that my pivot table isn't given the same result as you.
Do I need to create maybe a table of material number and a table of release number, and link it to both files?
Because I saw that you used the material number and release number now from waterfallReport2?
And or do I need to create as well a table of the "KEY" as well and link to both files? Will this be a proper way to work, or is this not needed?
Now I had only 3 examples in the file, but I will have 50, or even more then 100 materials in the file.
But not all will have for example release number 270, for some they can start with release number 1 for release date July 27th.
Again, thanks a lot for your support
Trusted Members
Moderators
January 31, 2022
Offline
Hi Johan,
Difficult to judge what needs to be done and what not. I took your data and guessed that my pivot table was what you wanted. I couldn't arrive to it other than by creating unique material keys and genera. If it works for three examples, it should work for 50 as well. but, I'm not sure that I totally understand what you are doing.
In general terms you need to separate transactional data (fact tables) from "general" data (dimension tables). So, perhaps yes it's best to create a table for all unique Materials codes with their characteristics. And a table for all Release numbers. together with the Date table, these will be on the 1-side of the relationships with the fact table "Waterfall", which will be on the many-side of the relationship.
Then, in the pivot table all row and column fields come from the dimension tables and all values from the fact table(s).
Redone the file as I noticed that I had violated these rules myself. But again, not sure if this will work for you on a larger scale. Try and let me know if it doesn't and why you think so.
Riny
1 Guest(s)
