topic Re: AGGR Function and Count in QlikView

AGGR Function and Count

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: AGGR Function and Count

YoussefBelloum — Wed, 11 Oct 2017 08:45:02 GMT

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))

Re: AGGR Function and Count

sunny_talwar — Wed, 11 Oct 2017 08:49:59 GMT

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)

Re: AGGR Function and Count

Anonymous — Wed, 11 Oct 2017 09:42:45 GMT

Hi Sunny

It works perfectly. Thanks alot.

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

Regards

Stefan

Re: AGGR Function and Count

sunny_talwar — Wed, 11 Oct 2017 11:14:26 GMT

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

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

Re: AGGR Function and Count

Anonymous — Wed, 11 Oct 2017 11:24:00 GMT

It makes perfect sense.

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

Thanks again