4 Replies Latest reply: Mar 26, 2016 6:24 AM by Stefan Wühl RSS

    Set Analysis: add and exclude values on same field

    Wim Rijken

      Hello,

      I want to create a list of customer which have revenue in a certain period, but at the same time have no revenue in another period.

      The periods are variable via 2 slider-objects.

       

      I have very simple sample data:

      fact:

      LOAD * INLINE [

          PostingDate, CustomerNo, Revenue

          01/01/2015, KL1, 1000

          01/01/2016, KL1, 1500

          01/04/2015, KL2, 800

          01/11/2015, KL2, 500

          01/03/2015, KL2, 300

      ];

       

      I have created 2 sliderobjects with min and max value:

      Slider object 1 fills 2 variables: vMinDateValueRevenue / vMaxDateValueRevenue

      Slider object 2 fills 2 variables: vMinDateValueNoRevenue / vMaxDateValueNoRevenue

       

      I have created a straight table with dimension "Customer" and expression "Revenue".

      Now I want to show as result:

      Sum of Revenue within the selected period between vMinDateValueRevenue and vMaxDateValueRevenue, but only if there is NO revenue in the period between vMinDateValueNoRevenue en vMaxDateValueNoRevenue.

       

      I assume this can be done via Set Analysis, but I cant find the correct syntax.

      Can anybody assist?

        • Re: Set Analysis: add and exclude values on same field
          Sunny Talwar

          For your sample what would be good values for setting the variables and the expected output?

          • Re: Set Analysis: add and exclude values on same field
            Stefan Wühl

            Maybe something like

             

            =Sum({<

            PostingDate = {">=$(=Date(vMinDateValueRevenue))<=$(=Date(vMaxDateValueRevenue))"},

            CustomerNo = {"=Sum({<Posting Date = {">=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))"}>}Revenue)=0"}

            >} Revenue)

             

            Double check that the date variables are correctly expanded in the set modifier:

            Dates in Set Analysis

             

            Same could maybe also be achieved using p() and e() function:

            =Sum({<

            CustomerNo =

            p({<PostingDate = {">=$(=Date(vMinDateValueRevenue))<=$(=Date(vMaxDateValueRevenue))"} >} )

            *

            e({<Posting Date = {">=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))"} >} )

            >} Revenue)

              • Re: Set Analysis: add and exclude values on same field
                Wim Rijken

                In the first solution, there seems to be a problem (the last part of the syntax of "CustomerNo" is underlined in red

                CustomerNo = {"=Sum({<Posting Date = {">=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))"}>}Revenue)=0"}

                 

                the second solution seems to be working perfectly.

                 

                Thanks!!

                  • Re: Set Analysis: add and exclude values on same field
                    Stefan Wühl

                    Ah, I think that's because nested advanced searches (advanced search enclosed  in double quotes embedded into advanced search enclosed in double quotes). You need to replace one pair with single quotes:

                     

                    CustomerNo = {"=Sum({<Posting Date = {'>=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))'}>} Revenue)=0"}

                     

                    After I posted my last answer I reviewed and then assumed that the first one should be logically correct, while the second one would filter the correct CustomerNo, but should not restrict the Revenue to the selected date range.

                     

                    I think this should be correct for this requirement:

                     

                    =Sum(

                    {<

                    PostingDate = {">=$(=Date(vMinDateValueRevenue))<=$(=Date(vMaxDateValueRevenue))"},

                    CustomerNo =

                              e({<Posting Date = {">=$(=Date(vMinDateValueNoRevenue))<=$(=Date(vMaxDateValueNoRevenue))"}>})

                    >} Revenue)