4 Replies Latest reply: Jun 17, 2011 9:16 AM by rahulr19480 RSS

    How To - Help with Set Analysis

      Hi All,

       

      Could I get some help here with a QV expression I am struggling with.

       

      I need a count of all Customers who have had no sales between a specific time period (from and to date).

       

      I have defined the from and two dates as variables (vCustLost and vEvalDate). I tried the below formula but the value is shown always as zero

       

      = Count ( {1<SO_KNA1.Customer = {"=Sum({1<%POSTDATE = {'>=$(=Date(vCustLost))<=$(=Date(vEvalDate))'} COPADAY.EURNetSales) < 0"}>} Distinct SO_KNA1.Customer )

       

      Table Key:

      SO_KNA1.Customer = Customer Account Number

      %POSTDATE = Sales Recognition Date

      COPADAY.EURNetSales = Sales Value

        • Re: How To - Help with Set Analysis
          John Witherspoon

          Looks like just syntax errors to me.  If your variables are really dates, I think this:

           

          count({1<SO_KNA1.Customer={"=sum({1<%POSTDATE={">=$(vCustLost)<=$(vEvalDate)" }>} COPADAY.EURNetSales)<0"}>} distinct SO_KNA1.Customer)

           

          If they're really numbers, I think this:

           

          count({1<SO_KNA1.Customer={"=sum({1<%POSTDATE={">=$(=Date($(vCustLost)))<=$(=Date($(vEvalDate)))"}>} COPADAY.EURNetSales)<0"}>} distinct SO_KNA1.Customer)

            • Re: How To - Help with Set Analysis

              Hi John,

               

              Thanks for the response but this still shows a syntax error. Both of them still show a Syntax error

                • Re: How To - Help with Set Analysis
                  John Witherspoon

                  Are you able to post the application?  You can scramble the information if it's sensitive.  I've never done it, but it looks like you can do it under document properties -> scrambling.

                    • Re: How To - Help with Set Analysis

                      Hi John,

                       

                      Actually i found the ans to the error. here is the formaula which works

                       

                      = Count ( {1<SO_KNA1.Customer = {"=Sum({1<%POSTDATE = {'>=$(=Date(vCustLost))<=$(=Date(vEvalDate))'} >} COPADAY.EURNetSales) < 0"}>} Distinct SO_KNA1.Customer )

                       

                       

                      Now i have another query. Can you tell me what I am doing wrong in the below query.

                      What I am trying to do is get a count of customers if their sales during one time period is less than 30% of the average sales between another time period where again the time periods are defined within variables. The part I added to the above formula is in red

                      = Count ( {1<SO_KNA1.Customer = {"=Sum({1<%POSTDATE = {'>=$(=Date(vCustNew))<=$(=Date(vEvalDate))'} >} COPADAY.EURNetSales <

                       

                       

                      (0.3*(Avg({1<%POSTDATE = {'>=$(=Date(vCustRisky))<=$(=Date(vEvalDate)))'} >} COPADAY.EURNetSales)))

                      "}>} Distinct SO_KNA1.Customer )