Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

larsquaade
New Contributor

Help to count "favorite" products

I have the following problem:

Loaded Table_data:

CustomerID

ProductID

OrderID   

SalesValue

Qty // quantity

OrderDate


I have created a Chart Table with:

dimension: [CustomerID]

measure: FIRSTSORTEDVALUE ( [ProductID] , -AGGR(SUM( [SalesValue]), [CustomID], [ProductID] )))

This is a list of each customers "favorite" product (measured by SalesValue). Now I would like to add the following measure to the created Chart Table: The total sales value each customer has spend on his specific favorite product.

I have tried

SUM({<[ProductID]={"=FIRSTSORTEDVALUE ( [ProductID] , -AGGR(SUM( [SalesValue]), [CustomerID], [ProductID] ))"}>}[SalesValue])

but this formula is incorrect and actually returns the customers total sales i.e. equivalent to SUM( [SalesValue] ).

Any help welcome; how should the correct formula look like? and please help me understand why the above set-formula just returns SUM ([SalesValue]) ?

Expanding on this, any suggestion on how to count the customers who has chosen [ProductID] as a favorite, i.e. a Chart Table with only the following

dimension: [ProductID]

measure: count the number of (distinct) customers who has chosen [ProductID] as a Favorite                  

Regards

Lars

1 Solution

Accepted Solutions

Re: Help to count "favorite" products

Try this

Sum(Aggr(If(ProductID = FirstSortedValue(TOTAL <[CustomID]> [ProductID], -Aggr(Sum([SalesValue]), [CustomID], [ProductID]))), Sum(SalesValue)), [CustomID], [ProductID]))

2 Replies

Re: Help to count "favorite" products

Try this

Sum(Aggr(If(ProductID = FirstSortedValue(TOTAL <[CustomID]> [ProductID], -Aggr(Sum([SalesValue]), [CustomID], [ProductID]))), Sum(SalesValue)), [CustomID], [ProductID]))

larsquaade
New Contributor

Re: Help to count "favorite" products

Formula works - thank you.

Community Browser