Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count customer by product family

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 FamilyCustomer CategoryCustomerRevenue
ACat-AAPPLE1300
ACat-BORANGE10000
BCat-BPEAR11000
CCat-AAPPLE140000
DCat-BPAPAYA30000
DCat-ACHERRY50000

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

Product FamilyCustomer Count
A1
B1
C0
D1

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!

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

17 Replies
daveamz
Partner - Creator III
Partner - Creator III

Hi Alice,

Have a look at the attached document.

Regards,

David

tajmohamed30
Creator III
Creator III

count({$<[Customer Category]={'Cat-B'}, Revenue={">=10000"}>}distinct(Customer))

Not applicable
Author

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

Not applicable
Author

Hi Taj,

It does not work...

Thanks,

Alice

sunny_talwar

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)

Not applicable
Author

Hi Sunny,

It does not work. The "Revenue" I have actually is an expression: SUM({<$(vMRevenueSet)>}$(vMRevenueField)). Would it be the reason?

Thanks,

Alice

sunny_talwar

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)

Not applicable
Author

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

sunny_talwar

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