topic Re: How To - Help with Set Analysis in QlikView

How To - Help with Set Analysis

Wed, 15 Jun 2011 16:17:37 GMT

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

johnw — Wed, 15 Jun 2011 17:30:13 GMT

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

Thu, 16 Jun 2011 13:46:36 GMT

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

johnw — Thu, 16 Jun 2011 16:33:35 GMT

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

Fri, 17 Jun 2011 13:16:03 GMT

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 )