Pro Rata on same key field

[mattdt1811]

Hi All,

Please find example data set, I shall try my best to explain!

In my current data (red on excel) I have one key field with different pieces and locations associated to it. If users have done their jobs properly, when they split pieces (so pieces break up and move locations) they should pro rata the weight and cbm down (like rows 2 & 3). Unfortunately this is not always the case (see rows 4 & 5) - the record pulls in the same weight and cbm as this is the only record present (for both lines)

The green on the example is what I am trying to get achieve. I need to Sum the pieces (when the weight & CBM are the same), divide the total weight and cbm by the total number of pieces, to get weight and cbm per piece, and then multiply by the individual line number of pieces to get my pro rata'd figure.

I tried - ((Weight)/ Sum(TOTAL([Pieces])) * [Pieces]) but this calculates across the whole key field (rows 2 - 5). 

Is someone able to help?

Thanks.

[Brett_Bleess]

Matt, have a look at the Design Blog link below, I think it may be along the lines of what you are looking for here, but I am not certain.  My post will kick things back up as well, so someone else may see things again and offer up something else too.

https://community.qlik.com/t5/Qlik-Design-Blog/Slowly-Changing-Dimensions/ba-p/1464187

Regards,
Brett

[mattdt1811]

Hi Brett,

Thanks for the response and I will be sure to give this a read.

In the mean time I have attached a sample QVW with some of my data.

A simple example of my issue would be consignment keys 5320871 & 5354435.

A more complex one being 5283306 – where some of the tags have had their weight split correct but locations 18B and 22B have not.

Hopefully this will help give a better idea to yourself / others who stumble across this.