Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
mattdt1811
Contributor III
Contributor III

Pro Rata on same key field

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.

 

Labels (4)
2 Replies
Brett_Bleess
Employee
Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
mattdt1811
Contributor III
Contributor III
Author

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.