topic Re: Need a help in set analysis in QlikView

Need a help in set analysis

Fri, 30 Nov 2012 20:21:36 GMT

Dear Community.

i need a help to write a set analysis.

i want to count the active customers from last three months Table.

sql query:

Select count(Distict CustomerCode) where Month='Aug','Sep','Oct' and Sum(Gross Sales)<>0

i tired in qlikview by using set analysis and if conditions but it's not giving correct result.

so please somebody help me to solve the issue.

Thanks,

Mukram.

Re: Need a help in set analysis

MayilVahanan — Fri, 30 Nov 2012 20:23:47 GMT

Count({<Month={'Aug','Sep','Oct'}>}Distinct CustomerCode)

Sum({<GrossSales ={">0"}>}GrossSales)

Hope it helps

Re: Need a help in set analysis

Fri, 30 Nov 2012 20:30:50 GMT

Dear Mayil vahanan.

thanks for your help.

i need to write those two expression in one expression.

means

the active customers are those who are having Sum(Gross sales) more than zero from last three months.

Thanks,

Mukram.

Re: Need a help in set analysis

MayilVahanan — Fri, 30 Nov 2012 20:37:38 GMT

Try like this

Do you have date field?if so,

Sum({<GrossSales ={">0"},Date = {'>=$(=MonthStart(AddMonths(Max(Date),-3)))<=$(=MonthEnd(AddMonths(Max(Date),-1)))'}>}GrossSales)

Hope it helps

Re: Need a help in set analysis

Fri, 30 Nov 2012 20:45:53 GMT

Dear,

Yes i have date field .

but i want to count the Active customer.

Thanks,

Mukram.

Re: Need a help in set analysis

CELAMBARASAN — Fri, 30 Nov 2012 21:05:37 GMT

Try with this

Count({<CustomerCode={"=Sum({<Month={'Aug','Sep','Oct'}>}GrossSales)<>0"}>} Distinct CustomerCode)

For dynamic month calculation you need to go with DateField calculation

Count({<CustomerCode={"=Sum({<DateField = {'>=$(=MonthStart(Max(DateField),-3))<=$(=MonthEnd(Max(DateField),-1))'}>}GrossSales)<>0"}>} Distinct CustomerCode)

Hope it helps

Re: Need a help in set analysis

MayilVahanan — Fri, 30 Nov 2012 21:06:18 GMT

Try like this

Edit:

Count({<CustomerCode={"=Sum({<DateField = {'>=$(=MonthStart(AddMonths(Max(DateField),-3)))<=$(=MonthEnd(AddMonths(Max(DateField),-1)))'}>}GrossSales)<>0"}>} Distinct CustomerCode)

Hope it helps