AGGR Function and Count

[Anonymous]

Hi All

I've been trying several different ways to get below expression to work.

However, I can't seem to figure out what I'm doing wrong? The result of below expression is 0.

The expression is comparing two periods of 12 months against each other, and if the customer have bought in both periods, it should be counted?

Count(AGGR(Count(DISTINCT 
IF(sum({$<CAL_YEAR =, CAL_MONTH =, DATE={'>=$(=MonthStart(Today(),-12))<$(=MonthStart(Today()))'}>}IF(National <> 'LOC',New_NET)) > '0'

AND sum({$<CAL_YEAR =, CAL_MONTH =, DATE={'>=$(=MonthStart(Today(),-24))<$(=MonthStart(Today(),-12))'}>} IF(National <> 'LOC',New_NET)) > '0',

Customer_Number)),Customer_Number))

Can anyone see where the problem is?

Regards

Stefan

[YoussefBelloum]

Hi,

maybe this:

Count(AGGR(Count(DISTINCT 
IF(sum({$<CAL_YEAR =, CAL_MONTH =, DATE={'>=$(=MonthStart(Today(),-12))<$(=MonthStart(Today()))'}, National-={'LOC'}>} New_NET)) > '0'

AND sum({$<CAL_YEAR =, CAL_MONTH =, DATE={'>=$(=MonthStart(Today(),-24))<$(=MonthStart(Today(),-12))'}, National-={'LOC'}>} New_NET)) > '0',

Customer_Number)),Customer_Number))

[sunny_talwar · Accepted solution]

May be try this:

Count(DISTINCT {<Customer_Number = {"=Sum({$<CAL_YEAR =, CAL_MONTH =, DATE={'>=$(=MonthStart(Today(),-12))<$(=MonthStart(Today()))'}, National -= {'LOC'}>} New_NET) > 0 and Sum({$<CAL_YEAR =, CAL_MONTH =, DATE={'>=$(=MonthStart(Today(),-24))<$(=MonthStart(Today(),-12))'}, National -= {'LOC'}>} New_NET) > 0"}>} Customer_Number)

[Anonymous]

Hi Sunny

It works perfectly. Thanks alot.

However, I'm not sure how to understand your expression?

Regards

Stefan

[sunny_talwar]

I am just counting all customers which have these two conditions true

Count(DISTINCT {<Customer_Number = {"=Sum({$<CAL_YEAR =, CAL_MONTH =, DATE={'>=$(=MonthStart(Today(),-12))<$(=MonthStart(Today()))'}, National -= {'LOC'}>} New_NET) > 0 and Sum({$<CAL_YEAR =, CAL_MONTH =, DATE={'>=$(=MonthStart(Today(),-24))<$(=MonthStart(Today(),-12))'}, National -= {'LOC'}>} New_NET) > 0"}>} Customer_Number)

This method is basically a search string method of using set analysis where customer_number will be evaluated against a Boolean. If the two statements are true, then the customer is going to be counted, if not, then they won't be counted.

Does this explanation help?

Best,

Sunny

[Anonymous]

It makes perfect sense.

It's a very clever way to do it. It will probably come in handy in the future.

Thanks again