3 Replies Latest reply: Aug 9, 2013 9:51 AM by Adam Haithcox RSS

    Concatenated value in set analysis

    Adam Haithcox

      Can someone tell me why this doesn't work:

       

      Sum({<DiscountPercentKey={"$(=[Customer Number]&'_'&[Item Category Code 2])"}>}[Discount Percent])

       

      Here are the values that I'm looking to return:

      Capture.PNG

      When I put the concatenation directly in the expression like this: [Customer Number]&'_'&[Item Category Code 2]  ; I get this:

      Capture2.PNG

      If I put the hard coded value in like this: Sum({<DiscountPercentKey={'14546_FUN'}>}[Discount Percent])  ; I get my percentage returned. I've checked that it's trimmed. What am I doing wrong?

        • Re: Concatenated value in set analysis
          Adam Haithcox

          I've removed the underscore used in the concatenation from the load script to eliminate that. It didn't help. I then added the additional "=" before the $. Now, I have the following expression that returns the TOTAL sum for all discounts listed for a single customer:

           

          Sum({<DiscountPercentKey={"=$(=[Customer Number] & [Item Category Code 2])"}>}[Discount Percent])

           

          Why am I getting the total sum for all percentages?

            • Re: Concatenated value in set analysis
              Juan Olivares

              As I see in this case you need to agregate your values in order to groups them, the set analysis maybe is not your best solutions.

               

              Try...

              Sum(Aggr(Sum([Discount Percent]),DiscountPencentKey))...

               

              See what happen

                • Re: Concatenated value in set analysis
                  Adam Haithcox

                  Thank you for the reply Jolivares! Unfortunately that doesn't work. I've since updated my table though. I now have a pivot chart with the following:

                   

                  Item Category Code 2, Item Number, List Price

                  CAT, Item1, $25

                  DIL, Item2, $30

                  EPI, Item3, $40

                  etc.......

                   

                  I want to add discount percent as an expression (or dimension) to calculate final price off of List Price. My discounts are based on Customer > Discount Category > Discount Percent. So each customer gets an individual discount per item category. That table (Discounts) has an association to my customers table. So the Items table and Discounts tables aren't linked. BUT, the Item Category Code 2 in the Items table has the same values as Discount Category in the Disounts table. Because I limit the pivot chart to one selected customer, the categories in the Discounts table gets limited to only the categorys > discounts for the selected customer:

                   

                  Discount Category, Discount Percent

                  CAT, 0.7

                  DIL, 0.65

                  EPI, 0.7

                   

                  So, now I'm trying to do something like Sum({$<[Discount Category]={$(=[Item Category Code 2])}>}[Discount Percent])  which I know from other posts isn't possible. I just don't know another way to do it.

                   

                  Any further help would be greatly appreciated.