Forum

Running Total diffe...
 
Notifications
Clear all

Running Total different criteria

7 Posts
3 Users
0 Reactions
172 Views
(@jvdw)
Posts: 15
Eminent Member
Topic starter
 

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  

 
Posted : 13/08/2022 9:09 am
Riny van Eekelen
(@riny)
Posts: 1196
Member Moderator
 

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

 
Posted : 13/08/2022 12:02 pm
(@jstewart)
Posts: 216
Estimable Member
 

Hi Johan!

While waiting for your example file, here is an article on how to do running totals in PQ

 
Posted : 13/08/2022 12:22 pm
(@jvdw)
Posts: 15
Eminent Member
Topic starter
 

Hey Riny,

Sorry, I though that I added the file.

 
Posted : 13/08/2022 1:17 pm
Riny van Eekelen
(@riny)
Posts: 1196
Member Moderator
 

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

 
Posted : 14/08/2022 3:45 am
(@jvdw)
Posts: 15
Eminent Member
Topic starter
 

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

 
Posted : 16/08/2022 2:20 am
Riny van Eekelen
(@riny)
Posts: 1196
Member Moderator
 

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

 
Posted : 16/08/2022 3:48 am
Share: