Qlik Community

Qlik GeoAnalytics Discussions

Discussion Board for collaboration regarding Qlik GeoAnalytics.

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!

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Count customer by product family

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

17 Replies
daveamz01
Contributor III

Re: Count customer by product family

Hi Alice,

Have a look at the attached document.

Regards,

David

tajmohamed30
Contributor III

Re: Count customer by product family

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

Not applicable

Re: Count customer by product family

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

Re: Count customer by product family

Hi Taj,

It does not work...

Thanks,

Alice

MVP
MVP

Re: Count customer by product family

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

Re: Count customer by product family

Hi Sunny,

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

Thanks,

Alice

MVP
MVP

Re: Count customer by product family

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

Re: Count customer by product family

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

MVP
MVP

Re: Count customer by product family

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

Community Browser