topic Aggregated Products per Customer ID in App Development

Aggregated Products per Customer ID

Wolfmeyn — Mon, 15 Apr 2024 08:47:01 GMT

Hi i have loaded some data through SQL editor.

I have created a table in Qlik where i have these columns:

- Customer ID

- Product Category

- Count (Product Category)

I WANT TO CREATE A COLUMN THAT SUM PRODUCTS PER CUSTOMER ID

SO THAT THE PICTURE I HAVE ATTACHED SHOULD SHOW THAT CUSTOMER ID 123 = 5

MATC — Mon, 15 Apr 2024 09:15:35 GMT

You can either add this to your script and use "Summary" dimension

tmp:
Load * Inline
[
CustomerID, ProductCategory
123, 'ab'
123, 'ab'
123, 'ac'
123, 'ac'
123, 'ac'
125, 'ac'
145, 'ad'
145, 'ad'
];

left join (tmp)
load
count(ProductCategory ) as Summary,
CustomerID
Resident tmp
group by CustomerID;

or add this to your table in the front as dimension as well

=aggr(count(ProductCategory),CustomerID)

hic — Mon, 15 Apr 2024 09:17:04 GMT

If you have both Customer ID and Product Category as dimensions in the chart, you can use the following to calculate number of product categories:

Count(total <[Customer ID]> [Product Category])

But it is simpler if you just have Customer ID as dimension. Then

Count([Product Category])

will work.