4 Replies Latest reply: Apr 24, 2013 3:35 AM by Bart De Bie RSS

    Set analysis remove results

      Dear all,

       

      I currently have a set analysis syntax which is working and also giving me a list of all results. What I need now, is to remove a small selection from this list.

       

       

      My current syntax is the following:

      sum( { $<Month={"=max(Month)"}, Year={"=max(Year)"} > } MachinePurchaseCost)

      +

      sum({$<InvoiceMonthYear={"$(=$(vMonthYear))"},MonthYearNum ={">= $(=Max(MonthYearNum))"},Month=,Year=>}MachinePurchaseCost)

       

      Now I want to remove some results from the above result. With the following syntax I'm getting the results that I don't want to see.

      sum({$<Month={"=max(Month)"},Year={"=max(Year)"},DirectShipFK={"1"},InvoiceMonthYearNum={"<$(=Max(MonthYearNum))"}>}MachinePurchaseCost)

      How can I make the syntax so that it will remove these results?

       

      Anyone have an idea how I can achieve this?

       

      Thanks in advance.

        • Re: Set analysis remove results
          Gysbert Wassenaar

          Perhaps this:

           

          sum({ $<Month={"=max(Month)"}, Year={"=max(Year)"}>

           

                   + <InvoiceMonthYear={"$(=$(vMonthYear))"},MonthYearNum ={">= $(=Max(MonthYearNum))"},Month=,Year=>

           

                   - <Month={"=max(Month)"},Year={"=max(Year)"},DirectShipFK={"1"},InvoiceMonthYearNum={"<$(=Max(MonthYearNum))"}>

           

              } MachinePurchaseCost)

            • Re: Set analysis remove results

              Thanks, This one did the trick. My final result looks like

               

               

               

              sum({ $<Month={"=max(Month)"}, Year={"=max(Year)"}>


                       + <InvoiceMonthYear={"$(=$(vMonthYear))"},MonthYearNum ={">= $(=Max(MonthYearNum))"},Month=,Year=>


                       - <Month={"=max(Month)"},Year={"=max(Year)"},DirectShipFK={"1"},InvoiceMonthYearNum={"<$(=Max(MonthYearNum))"}>

                 

                       - <Month={"=max(Month)"},Year={"=max(Year)"},DirectShipCE={"1"},InvoiceMonthYearNum={"<$(=Max(MonthYearNum))"}> 
              } MachinePurchaseCost)

            • Re: Set analysis remove results
              jagan mohan rao appala

              Hi,

               

              Try like this

               

              =(sum( { $<Month={"=max(Month)"}, Year={"=max(Year)"} > } MachinePurchaseCost)

              +

              sum({$<InvoiceMonthYear={"$(=$(vMonthYear))"},MonthYearNum ={">= $(=Max(MonthYearNum))"},Month=,Year=>}MachinePurchaseCost) ) -

               

              sum({$<Month={"=max(Month)"},Year={"=max(Year)"},DirectShipFK={"1"},InvoiceMonthYearNum={"<$(=Max(MonthYearNum))"}>}MachinePurchaseCost)