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!
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 Alice,
Have a look at the attached document.
Regards,
David
count({$<[Customer Category]={'Cat-B'}, Revenue={">=10000"}>}distinct(Customer))
Hi David,
I do not see any expression for customer count in the .qvw file you attached. Can you help me on the expression?
Thanks,
Alice
Hi Taj,
It does not work...
Thanks,
Alice
I think this should be enough... don't think we need a TOTAL <...> here
Count({<[Customer Category] = {'CAT-B'}, Customer={"=Sum(Revenue)>=10000"}>}DISTINCT Customer)
Hi Sunny,
It does not work. The "Revenue" I have actually is an expression: SUM({<$(vMRevenueSet)>}$(vMRevenueField)). Would it be the reason?
Thanks,
Alice
May be this... not 100% sure because I am not sure about how you define your variables vMRevenueSet and vMRevenueField
Count({<[Customer Category] = {'CAT-B'}, Customer = {"=Sum({<$(vMRevenueSet)>}$(vMRevenueField))>=10000"}>}DISTINCT Customer)
I have tried this but it does not work.
Actually I had simplified the field names in my first post yesterday.
The below is the expression with actual field names I have now, which works correctly when I select only 1 product family. When I do not apply filter on product family, the customer count results become incorrect.
COUNT(DISTINCT
AGGR(
IF (
SUM({<$>*<
FORECAST_CUST_CAT={"CAT B DISTRIBUTION","CAT B DIRECT"},
$(vMRevenueSet)>}
$(vMRevenueField))>=10000, END_CUSTOMER),
END_CUSTOMER))
Notes:
FORECAST_CUST_CAT is Customer Category
END_CUSTOMER is Customer
SUM({<$(vMRevenueSet)>}$(vMRevenueField)) is Revenue
Would you be able to share some mocked up data which closely aligns with your real data? Its difficult to see what might be going wrong just by looking at the expression you have