Count customer by product family

[#1]

Hi,

I would like to get customer count by product family from a set of data with below criteria:

     Customer category = Cat-B

     Total Revenue by Customer >= $10000

Sample data:

Product Family	Customer Category	Customer	Revenue
A	Cat-A	APPLE1	300
A	Cat-B	ORANGE	10000
B	Cat-B	PEAR	11000
C	Cat-A	APPLE1	40000
D	Cat-B	PAPAYA	30000
D	Cat-A	CHERRY	50000

The count result I expected for the above is as below:

Product Family	Customer Count
A	1
B	1
C	0
D	1

My current expression below only works correctly when i filter on just 1 Product Family. When I release the filter on Product Family, it returns incorrect count results. Please help.

COUNT(DISTINCT

  AGGR(

     IF (SUM ( {<$>*<CUSTOMER_CATEGORY ={"CAT-B"}>} REVENUE) >= 10000, CUSTOMER),

    CUSTOMER))

Thank you!

[#2]

Hi Sunny,

I have tried to create some mocked data in this attached app. I have included the expression for Revenue and also the Customer Count (which I need help on).

The Customer Count result that I would like to achieve is as per below table:

PRODUCT FAMILY	Customer Count
A	3
B	6
C	2

If you filter on 1 Product Family at a time, you can get the correct answer, but when you remove filter to show all Product Family, the count is incorrect.

Thanks!

Alice

[sunny_talwar · Accepted solution]

May be this

COUNT(DISTINCT AGGR(IF(SUM({$<FORECAST_CUST_CAT={"CAT B DISTRIBUTION","CAT B DIRECT"}, REV_CHANNEL ={"Y"}>} [DOLLARS]) >= 10000, END_CUSTOMER), [PRODUCT FAMILY], END_CUSTOMER))

[#4]

Hi Sunny,

It works fine in the mocked app (thanks very much!)

I tried to apply the same in my actual app with below but it does not work (return all "-").

Can you help me see what went wrong in the below expression?

COUNT(DISTINCT  AGGR( IF( SUM({<$>*< FORECAST_CUST_CAT={"CAT B DISTRIBUTION","CAT B DIRECT"}, OPS_REV_CHANNEL={"Y"}>}  [CR_DOLLARS])>=10000, END_CUSTOMER), [FAMILY], END_CUSTOMER))

Note:

OPS_REV_CHANNEL is the "REV_CHANNEL" in mocked data

CR_DOLLARS is the "DOLLARS" in mocked data

FAMILY is the "PRODUCT FAMILY" in mocked data

Thanks again,

Alice

[sunny_talwar]

Not sure, but may be this

Count(DISTINCT  Aggr(If(Sum({$<FORECAST_CUST_CAT = {"CAT B DISTRIBUTION","CAT B DIRECT"}, OPS_REV_CHANNEL = {"Y"}>}  [CR_DOLLARS]) >= 10000, END_CUSTOMER), [FAMILY], END_CUSTOMER))

Made changes to set analysis part

{<$>*< FORECAST_CUST_CAT -> {$<FORECAST_CUST_CAT

[#6]

Still the same, it doesn't work...

[sunny_talwar]

Don't really know

[#8]

It works now .

The reason it did not work earlier is due to I put too much comments in the expression window and some of them i did not realize that it was not being "commented".

Thanks so much Sunny!

[#9]

Hi Sunny,

Now I need to add one more condition/criteria for the customer count, i.e. data with invoice date within 1 year from today.

I amend the expression to below but it does not work. Please help. Thanks!

COUNT(

  AGGR(

    IF(

            SUM({<$>*<

            INVOICE_DATE > DATE(TODAY())-365,

            INVOICE_DATE <= DATE(TODAY())-0,

            FORECAST_CUST_CAT={"CAT B DISTRIBUTION","CAT B DIRECT"},

            OPS_REV_CHANNEL={"Y"}>}

  [CR_DOLLARS]) >=10000, END_CUSTOMER), FAMILY,

    END_CUSTOMER))

Note: the Invoice_Date is in xxxxx format.