15 Replies Latest reply: Jul 25, 2017 4:58 AM by Charlie Leo RSS

    Set Expression

    Charlie Leo

      sum(if(vColl='A' and YearMonth = text(Timestamp(Today,'YYYYMM')),L1SBSrcTgtAmount+L2SBSrcTgtAmount,

      if(vColl='C' and   YearMonth = text(Timestamp(Today,'YYYYMM')) ,L1SBSrcTgtCount+L2SBSrcTgtCount)))


      someone please change in to setanalysis.



      thanks In advance





        • Re: Set Expression
          Sunny Talwar

          Would you be able to share a sample where you doing this? What we provide, may or may not work, it would be helpful to see a sample where you are using the if and convert and test the set analysis

          • Re: Set Expression
            Alexandra Costa

            Hi Charlie,


            Your IF clause has two arguments, the first one is about the value of vColl and the second one is about the YearMonth value. To change them to Set Analysis, we have to do the folowing:


            vColl='A'    turns to    vColl={'A'}

            vColl='B'    turns to    vColl={'B'}

            YearMonth = text(Timestamp(Today,'YYYYMM'))    turns to    YearMonth={$(=Date(Today(), 'YYYYMM'))}


            So we will have two set analysis conditions:

            {$<vColl={'A'}, YearMonth={$(=Date(Today(), 'YYYYMM'))}>}

            {$<vColl={'B'}, YearMonth={$(=Date(Today(), 'YYYYMM'))}>}


            Now, let's talk about the Sum. As set analysis "isolate" the sets, we can use the sum of 4 different subsets.

            Sum(L1SBSrcTgtAmount) + Sum(L2SBSrcTgtAmount)      =>  first set analysis condition

            Sum(L1SBSrcTgtCount) + Sum(L2SBSrcTgtCount)           =>  second set analysis condition


            So, the solution you are trying to achieve is something like this:


            Sum({$<vColl={'A'}, YearMonth={$(=Date(Today(), 'YYYYMM'))}>} L1SBSrcTgtAmount) +

            Sum({$<vColl={'A'}, YearMonth={$(=Date(Today(), 'YYYYMM'))}>} L2SBSrcTgtAmount) +

            Sum({$<vColl={'C'}, YearMonth={$(=Date(Today(), 'YYYYMM'))}>} L1SBSrcTgtCount) +

            Sum({$<vColl={'C'}, YearMonth={$(=Date(Today(), 'YYYYMM'))}>} L2SBSrcTgtCount)


            I hope I could help!




              • Re: Set Expression
                Charlie Leo

                Thanks Alex, but no luck , i appreciate your Effort .

                something Missing Alex..




                  • Re: Set Expression
                    Alexandra Costa

                    Yes. It is missing the ' in the date clause and I also optimised the formula:


                    Sum({$<vColl={'A'}, YearMonth={'$(=Date(Today, 'YYYYMM'))'}>} L1SBSrcTgtAmount + L2SBSrcTgtAmount) +

                    Sum({$<vColl={'C'}, YearMonth={'$(=Date(Today, 'YYYYMM'))'}>} L1SBSrcTgtCount + L2SBSrcTgtCount)


                    But I also understand that your IF clause cannot be replaced by this set analysis because what you want at the end is the sum of one set OR another. The formula that I provided SUMs both subsets. You can check that the second part of the formula: Sum({$<vColl={'C'}, YearMonth={'$(=Date(Today, 'YYYYMM'))'}>} L1SBSrcTgtCount + L2SBSrcTgtCount) gives you the same result of your original formula.


                    So, what we need is a mix from your formula and mine, like this one below:


                    If(vColl='A', Sum({$<YearMonth={'$(=Date(Today, 'YYYYMM'))'}>} L1SBSrcTgtAmount + L2SBSrcTgtAmount),

                    If(vColl='C', Sum({$<YearMonth={'$(=Date(Today, 'YYYYMM'))'}>} L1SBSrcTgtCount + L2SBSrcTgtCount))).


                    Best Regards,