Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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))
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
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
Still the same, it doesn't work...
Don't really know
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!
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.