Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to split sales of one segment to another segments

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

Tags (3)
2 Replies
Not applicable

How to split sales of one segment to another segments

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?

Not applicable

How to split sales of one segment to another segments

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 incorrectSmiley Sad

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



Community Browser