Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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