Count using Set Analysis

[David_K]

Hi Fellow Qlik Users,

I have a need to count brands sold based on there sales type, as well as sum the sales value of the brand sold by which sales category

Brand:

• Brand1
• Brand2

Category

• Cash
• Credit Card
• Debit Card
• Finance

I have tried using a set analysis expression as below, but cannot seem to get it to operate as expected

IF (Brand = 'BRAND 1', sum({< [Category] = 'Cash'}>} [Category]))

Can someone please provide a little guidance on how I can achieve my objective.

Many thanks,

David

[Kushal_Chawda]

@David_K What is the expected output based on different rules?

[David_K]

Hi Kushal,

The expected output would be the 

1. Count of Brand by Category

2 Sum of value of brand sold by category

Brand	Category	Count	Value
Brand 1	Finance	25	1,234
Brand 2	Credit Card	300	1,234,567
Brand 2	Cash	100	456,789

Does this help

Thanks

David

[Kushal_Chawda · Accepted solution]

@David_K  If you have brand and Category Available in your Data, you can simply create a table with Dimension Brand,Category with Below measures

Count = Count(Category)

Value= sum(Value)

[Vegar]

You have a missing bracket, {, in your expression. 

IF (Brand = 'BRAND 1', sum({< [Category] = {'Cash'}>} [Category]))

You should also be able to simplify this by including the Brand as a modifier, like below. 

sum({< Brand = {'BRAND 1'}, [Category] = {'Cash'}>} [Category])