2 Replies Latest reply on Feb 7, 2019 2:22 PM by Patrick Schenkel

    Partial Grouping or Allocating unknown to groups

    Patrick Schenkel

      I have dirty data from a very rigid system.  I have a dimension that is unknown that I need to allocate by historically documented percentages.  Is it possible to create a grouping from a percentage?  For example

      CountrySales
      USA1000
      Canada900
      Mexico500
      Russia1200
      Denmark800
      Unknown200


      Allocation based on historic patterns:

      Country to allocate toAllocation
      Russia50%
      USA30%
      Canada20%


      Desired Output:

      CountryNew Sales
      USA1000 + (30% * 200)=1060
      Canada900 + (20%*200) =940
      Mexico500
      Russia

      1200 + (50%*200)=1300

      Denmark800

       

      Any help is appreciated!

       

      Thanks,
      Pat

        • 1. Re: Partial Grouping or Allocating unknown to groups
          Jim Dehner

          Hi Patrick

           

          I would suggest creating an allocation to call 5 countries - you just have 2 that are 0  Mexico and Denmark - If this is a regular thing Suggest you keep it in a table and join that table your primary table then you could do something like  

          sales to allocate = {fixed : sum(if country="Unknown" then sum(sales)}

                   {fixed [country]: ( if min( [country]) <> 'Unknown" then sum(Sales)  + sum(sales to allocate) * {Allocation %) )}

           

           

          I don't have your txwb  workbook so I cant test the formulas - you may need to play with them a bit

           

          and depending on the actual data and the sheet you will probably have to play with the LOD dimensions before the colon

           

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: Partial Grouping or Allocating unknown to groups
            Patrick Schenkel

            Thanks Jim--

             

            Very helpful--Also got an assist from Josh Weybourne and Joost Rietdijk.  This is how the problem was solved.

             

            Calc 1:

            If Country = 'Unknown' then sales else 0 end


            Calc 2:

            {Fixed Country : Sum (Calc1)}

             

            Calc 3:

            if attr(country) = 'USA' then sum(sales) + sum(calc 2) * .3
            elseif attr(country) = 'Russia' then sum(sales) + sum(calc 2) * .5
            elseif attr(country) = 'Canada' then sum(sales) + sum(calc 2) * .2
            else     sum(sales) end

             

            Thanks,
            Pat