6 Replies Latest reply: Sep 23, 2009 9:41 PM by Amirali Vastani RSS

    Multiple set analysis conditions not working




      The first half of my set analysis statements works great, but trying to combine another standalone functioning set analysis statement I can not get a result

      Background explaination: This statement is based on distributor selection, It sums all values with the exception of the value selected.

      sum({<Distributor = {*}-{$(=chr(39) & Distributor & chr(39))}>}


      statement b:

      works in another chart. It calculates previous month based on month sequence calculation. In this chart I have three 3 fields: -1, -2, -3. .

      {<FYYear=,FYMonth=, FYMonthName=,MonthSequenceNum={$(=only(MonthSequenceNum)-1)}>} POS_Qty






      ({<Distributor = {*}-{$(=chr(39) & Distributor & chr(39))}>} + {<FYYear=,FYMonth=, FYMonthName=,MonthSequenceNum={$(=only(MonthSequenceNum)-1)}>} POS_Qty)

      Goal: I would to sum POS Qty for all other distributors and get the sum of the previous 3 months. I don't mind if I have to do this in three expression and then sum, but I was hoping to accomplish this in one expression.

      Any recommendations or suggestions?

        • Multiple set analysis conditions not working
          Oleg Troyansky


          I see two issues here - one is pure syntax, and another is logical.

          1. From the syntax standpoint, I don't think you can add Set Analysis expression the way you did... In other words, your Set Analysis expression should have one set of outer curved brackets. For example, expression {1-$} is valid, while {1} - {$} is not. Do you see the distinction?

          2. From the logical standpoint, even if your expression worked, it would've returned summarized sales for all the excluded customers OR if the month is within the last 3 months - your logical "+" signified a union between the two sets. Are you sure this is what you are truly after? If I understand your business problems, you'd like to know the sales for all excluded customers, that happened within the last 3 months. In this case, you don't need a union, you rather need to list your set modifiers one after another, separated by coma.



          sum({< Distributor = {*}-{$(=chr(39) & Distributor & chr(39))} , FYYear=,FYMonth=, FYMonthName=,MonthSequenceNum={$(=only(MonthSequenceNum)-1)} >} Sales)