How to split sales of one segment to another segments

[#1]

Dear QlikView users,

I have a big problem and I hope somebody could help me here. I need to split purchases made by customer group called 'BBB' into other customer groups. Depending on a product (I have three), sales have to be divided with a different ratios. For example: 60% of milk sales, within group 'BBB' have to be assigned to group "AAA', while remaining 40% have to be assigned into group 'CCC'. Nextly, in case of water sales, 100% of volume, within group 'BBB' have to be assigned into group 'CCC'.

I started with writing formula like that:

if([Product group]='Milk',sum({$<MarketSegmentGroup={CCC}>}[Net Sales]) ... and I have no idea how I could go further. In simple understanding it should be:

if(Product group)=Milk, for CCC sum sales plus 60& of sales 'BBB' and so on....

Does anybody have an idea how it could be solved?

Thank you in advance,

Beata

[#2]

I think this is something I would try do deal with in the Load script, do the proportions apply at a transaction level, or can you at a transaction level assign each value to it's new group.

What is your current load statement for this table?

[#3]

Hallo Nigel,

First of all, right now I came up with this expression:

if([Product group]='Milk' and MarketSegmentGroup='CCC',sum([Net Sales])+0.4*(sum({$<MarketSegmentGroup={'BBB'}>}[Net Sales])),sum([Net Sales]))

and I thought it's done, but the sum seems to be incorrect:(

Generally my Load is ver easy, and it's taken from Excel files (finished), so we work with the past data.

Main:

LOAD Company,

Jaar,

[Klant nummer],

Klantnaam,

Country,

Marktsegment,

[Product category],

[Product caetgory equal],

[Product nummer],

Productnaam,

[Sales grouping],

[TP volume],

[ICO volume],

[IG volume],

[Total volume],

[Net Sales],

[Gross Margin],

[Gross profit]

Countries:

LOAD Country,

[Country group]

MarketSegments:

LOAD Marktsegment,

[Group] as MarketSegmentGroup

LOAD [Product category],

[Product group],

[Product group detailed],

Thank you for effort,

Beata