How To - Help with Set Analysis

[#1]

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

[johnw]

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)

[#3]

Hi John,

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

[johnw]

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.

[#5]

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 )