2 Replies Latest reply: May 19, 2010 11:53 AM by beciafreestyle RSS

    How to split sales of one segment to another segments

    beciafreestyle

      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

        • 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?

            • How to split sales of one segment to another segments
              beciafreestyle

              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