Table, how to sum by client when values are in different cells

[patriciousa]

Hi everyone. I need help with 2 excels I'm working on. I started to use the app no more than a month ago, the syntaxis guide and this forum has been really useful but I'm here, stuck again. Found it really hard to make a coherent topic.

Please, follow the information and you will understand what I want.

I have these, 2 excels with the following info:

client table

customer	client	sub#
coca	4	1
pepsi	3	2
sprite	5	3
fanta	6	4
mirinda	7	5
water	8	6

AND

Sales table:

client name	client	sales Q
coca	4	44
pepsi	3	30
sprite	5	493
fanta	6	2
mirinda	7	2
water	8	13
coca	4	34
pepsi	3	413
sprite	5	405
fanta	6	40
mirinda	7	300
water	8	1050
coca	4	100500
pepsi	3	94

The client name in the Sales table is pretty much endless because it does not sum the "sales Q" column by client.

What I want to show in my table in Qlik Sense is the following:

client name	client	sales Q
coca	4	5444
pepsi	3	4430
sprite	5	2493
fanta	6	442
mirinda	7	13232
water	8	413

I tried a simple thing which was sum("sales Q") but it says Invalid Dimension. Maybe I should not be using the table or I'm doing it wrong....

Now. something else, I managed to paint the "sales Q" cells in Qlik Sense.

if(sum("sales Q")> 3000, 'green()', blue())

But I want to paint them by client, example:

If pepsi or coca is > 3000, red, blue

if sprite, fanta, mirinda, water are > 250, green, red.

How can I write this in the formula?

Thank you in advance.

Regards.

[swuehl]

To answer your first question, this should be easy:

1) Create a chart with dimension "client name"

1b) If needed, create a second dimension "client"

2) Create an expression

=Sum("sales Q")

[patriciousa]

I said I did that sum but it gives me Invalid Dimension.

[sunny_talwar · Accepted solution]

Is this you want?

Color expression goes here:

Color expression:

If(Match([client name], 'coca', 'pepsi'),

If(Sum([sales Q])> 3000, Red(), Blue()),

If(Sum([sales Q]) > 250, Green(), Red()))

[swuehl]

You just need to select the appropriate field name from the drop down list.

Where does it give you an error, could you post your app or screenshots?

[reddy-s]

Hi Patricio,

You can even make use of the ColorMix() function if you have more complex calculations while coloring.

Thanks,

Sangram Reddy.

[patriciousa]

Thank you Sunny-T