Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

4 Replies
MVP
MVP

Re: How To - Help with Set Analysis

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)

Not applicable

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

MVP
MVP

Re: How To - Help with Set Analysis

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.

Not applicable

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 )

Community Browser