Need a help in set analysis

[#1]

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.

[MayilVahanan]

HI

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

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

Hope it helps

[#3]

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.

[MayilVahanan]

HI

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

[#5]

Dear,

Yes i have date field .

but i want to count the Active customer.

Thanks,

Mukram.

[CELAMBARASAN]

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

[MayilVahanan]

Hi

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